One of the most common pieces of advice with respect to securing WordPress, Magento, and other widely-used pieces of software is to add a prefix to database table names or change the default prefix. For example, one frequently hears to change the default wp_ prefix for WordPress to something obscure, like 1sdf34jSqo8_.
The question I have is this: Isn't this no more than security by obscurity? And if so, is it a good practice to do it anyway?
The pros and cons that I see to this are:
Advantages:
- Many SQL injection attacks necessarily have two steps: identify the tables and then do something else. This doesn't prevent attacks like submitting a password like
xyz' OR 1=1 --', but it does prevent, say, a query likeDROP TABLE fooactually doing anything, until the attacker somehow learns the table name. - It helps mitigate the nightmare zero-day attack in which there really is a SQLi vulnerability. A little obscurity is helpful in those situations.
- It might make it a little easier to intercept and block certain attacks via mod_security or a WAF. For example, if my table prefix is
1sdf34jSqo8_and that string shows up in GET or POST data, I'm going to be very suspicious of that request. But then, if the attacker has that string, I'm likely pwned already. Seeing that text from anyone not already authenticated as a superuser is actually probably a direct-to-pager alert, in fact. - It enables installing multiple copies of an application in one database, for those instances in which a host or service provider allows you to have 1 or 2 databases but doesn't limit the number of tables (which has never made sense to me, but I digress). *This isn't really a security concern, true, but it's a major reason people enable prefixing like this.
Disadvantages:
- This is a huge pain to implement as a developer. It means every table reference in your code needs a constant or variable added to the table name. Simple SELECTs are bad enough; schema changes and patches can become real nightmares. This also breaks the ability of many IDEs to do things like autocompletion and spelling checks on SQL statements.
- Thanks to the above problems, it's arguably more likely to result in bugs and possible vulnerabilities due to that one time someone on your team wrote a statement like
SELECT * FROM {prefix}foo LEFT JOIN bar ...and forgot the prefix somewhere. - It obviously is at least a form of security by obscurity. This leads to sloppiness, overconfidence, etc.
- It doesn't solve any problems that prepared statements and whitelisting don't already fix.
I think the accepted answer to the question "Is it okay to reveal database's table names?" nicely summarizes my thoughts - you don't lose anything by revealing table names if your database is secure against injection. If there's a breach at another layer, like a successfully uploaded and executed rogue script, then (1) the table prefix will be easy to discover, and (2) you're hosed anyway.
So, the short version of the question: is this actually something developers should do when creating applications, or is it just a bad practice masquerading as an additional layer of security?
EDIT: To be clear, I'm not asking whether the end user should go ahead and change the prefixes for, say, a WordPress installation's database. I'm asking whether, when a developer is creating an application that uses a database, the developer should enable this kind of functionality in the first place.
