How to change the Database prefix after installation to improve security?

[drroot]

Could we do this from Back Office->Preference->Database?

 

Otherwise what is the safe SQL command to change the database prefix?

 

 

Thank you.

[drroot]

Beside of modifying config/settings.inc.php, anywhere else to change?

[drroot]

Could we use following bash script on the dumped the sql database?

 

#!/bin/sh
#
# Simple script for setting table prefixes in SQL-dump
if [ $# != 2 ]; then
cat >&2 << EOH

Usage:
$0 prefix original_db.sql >prefixed_db.sql
- all tables will prefixed with 'prefix'
EOH
exit 1;
fi
PRFX=$1;
sed "s/^CREATE TABLE /CREATE TABLE $PRFX/i;
s/^INSERT INTO /INSERT INTO $PRFX/i;
s/^REPLACE /REPLACE $PRFX/i;
s/^ALTER TABLE /ALTER TABLE $PRFX/i" $2

[newbie2PS]

Hi drroot,

 

After my store seems getting bad bots and now I'm try to figure things out too. I cam across with this blog http://blog.dh42.com/best-e-commerce-security/. And I have an idea to change the databse prefix too because I ask my hosting to insall prestashop for me. So I wonder if we can change the prefix now after the installation for many months. Have you had the solution yet?

 

Best Regards,

[vekia]

well, it is possible but in this case you need an access to some database manager software like phpmyadmin

if you've got - you can alter all tables there (change name of the table, which mean that you can change table prefix)

after that you have to define new table prefix in the settings.inc.php file (config/sertings.inc.php)

 

you've got there:

define('_DB_PREFIX_', 'ps_');

 

change the value to your new prefix defined in the phpmyadmin (while you altering tables)

[newbie2PS]

Dear Vekia,

Thank you very much for your reply. I wonder if changing the table prefix will increase the security to prestashop or not?

Best,

[vekia]

nope, table prefix isnt related to the security

[newbie2PS]

Thank you very much, Vekia

[selsiusx]

If I change the db prefix in settings.inc.php, still the old prefix is used...  (prestashop 1.5.6.2) is ther some cache I need to clear ?

[vekia]

you changed also table names? it's necessary to alter these tables

and regarding to cache: yes, you have to clear cache and recoimpile entire theme

[Ivak]

I've found simple method. Just open .sql file from Notepad ++. Find the first table name. Copy its prefix. Press Ctrl+H. Paste that copied prefix in the "find" text box. Then type "replace" text. Just click replace button once. Then again. Those two files replace without problem. Just press replace all button. It will replace your all prefix according to given condition.

[lxuancheng]

My /config/settings.inc.php is empty, where is saved my first prefix configuration?

[Afriluka]

It seems since Prestashop Version 1.7.0.0 it is stored in /app/config/parameters.php

Edited February 23, 2021 by Afriluka (see edit history)

[Afriluka]

I am using Prestashop 1.7.7.0 and I have changed my prefix from ps_ to a more unique one. I also changed the prefix in the file mentioned above. The Frontoffice is working well. But the backoffice is showing "HTTP ERROR 500". But when I turn the debug mode on, everything seems to work well. 

Does anyone has an idea what this could mean? And how to fix it?

Thank you in advance.

[daxit_x]

Hello

I am using prestashop 8.0.2 on PHP 8.1

I tried to change a custom table prefix I used during installation in favour of the default prefix ps_

I got exaclty the same error that Afriluka described in the previous post "The Frontoffice is working well. But the backoffice is showing "HTTP ERROR 500". But when I turn the debug mode on, everything seems to work well. "

So it seems that in prestashop 8 changing the tables' prefix, editing the /app/config/parameters.php file, clearing cache and recompile template files is not enough, I searched quite much on the forum, on the docs, in more search engines for some extra information and found none.

Does anyone know all what has to be done to change the database tables' prefix in prestashop 8?

Thank you

Edited April 12, 2023 by daxit_x (see edit history)

[fazilnlend]

Hello,

logged in phpadmin you can proceed via script (Alter table to rename) or simply proceed as below,

1. click on your database
2. Mark all tables which you want to change the prefix (in the bottom of the main screen you can find a “check all” box).
3. Near the "check all" box, in the drop down list select "Replace prefix".
4. Enter the "From" prefix that you intent to change, Enter the "To" prefix that you want to adopt (including "_" at the end as usual) and submit.
5. into /app/config/parameters.php (prestashop 1.7) or settings.inc.php (prestashop less version) Locate the line "database_prefix" and modify with "YourCurrentPrefix" then save

[daxit_x]

Hello

Yes fazilnlend, that procedure you describe is correct, but not complete at present day with PS 1.7 and PS 8x.

At least from prestashop 1.7.7.0 and definitely in prestashop 8x that procedure is not complete and will cause severe problems as reported e.g. by Afriluka

Quote

Afriluka - Posted February 23, 2021

I am using Prestashop 1.7.7.0 and I have changed my prefix from ps_ to a more unique one. I also changed the prefix in the file mentioned above. The Frontoffice is working well. But the backoffice is showing "HTTP ERROR 500". But when I turn the debug mode on, everything seems to work well.

and as reported now also by myself having very recently done extensive experiments on prestashop 8.0.2

Quote

daxit_x - Posted 12 April 2023

I got exaclty the same error that Afriluka described in the previous post "The Frontoffice is working well. But the backoffice is showing "HTTP ERROR 500". But when I turn the debug mode on, everything seems to work well. "

So it seems that in prestashop 8 changing the tables' prefix, editing the /app/config/parameters.php file, clearing cache and recompile template files is not enough, I searched quite much on the forum, on the docs, in more search engines for some extra information and found none.

I checked if anything regarding the tables prefix could be for any reason be written also inside an any table, as foreseen apparently nothing is written in the database, so I checked the whole filesystem and saw that the tables' prefix is noted also in a lot of files into the folder var/cache/prod, substituting the old prefix with the new prefix in all the files (over 200 files) seemed to have solved the problem, apparently, I am not sure yet.

I did some tests and saw no problems except than in relation to a module if debug mode is active on the front office I get a huge error message, but this could be related to that specific module only, I have no clear idea about that yet, I need to do more experiments and check e.g. if autoupgrade continues to work fine after these changes and so on.

in that var/cache/prod folder the files contained are all related to "symfony" framework, I have no idea of what that system does there, however at first glance looked alike that dynamically injects code stuff into the live code used by the website, just a guess, I have no real good idea.

Another thing I am guessing is that this symfony system cache is not deleted using the usual procedure by clicking the button in the advanced parameters > performance panel, I have no idea of how to reset the cache of this sysmfony system, I would like to do it as I am guessing that could be another way to to solve troubles arising after changing the tables' prefix.

Does anyone have more information about these issues?

Thank you in advance

[fazilnlend]

Hello Daxit_x,

Thanks for the feedback. i faced the error 500 (true that it was not after a change of prefix) following different change i made time to time and below are some suggestions :

 

• Try to turn off URL rewriting (you can turn on back later)
• Try to Restore a backup of the .htaccess file that your system was working with before the change made
• Try to rename (since your shop is ben visited in the same time by customers) then erase these two directories DEV and PROD to clear cache.

NB: Always remember to keep a recent backup of the system to be able to restore in case of issues.

[jetx]

On 4/13/2023 at 8:27 PM, daxit_x said:

in that var/cache/prod folder the files contained are all related to "symfony" framework, I have no idea of what that system does there, however at first glance looked alike that dynamically injects code stuff into the live code used by the website, just a guess, I have no real good idea.

Just delete this folder. 

Simply replace the data prefix using phpmyadmin, edit your parameters file prefix and delete the cache folder (prod).

[WeMobiles]

We faced the same issue in Prestahsop 8.1, to fix that removed the prod and dev folder in var/cache, and then reloaded the browser.

that's it.

 

Thank You

wemobiles