8

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 like DROP TABLE foo actually 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.

7

Most modern databases store metadata in regular tables (Information Schema) so if there is an SQL injection the attacker will eventually find out every necessary information for exploitation, including your naming schemes.

In case of Wordpress and other popular software custom settings may help dodge some automated attacks, as primitive exploits may assume that standard names are used. However if the attacker uses a bit more advanced tool like SQLmap, the custom naming will be revealed almost instantly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.