35

I see systems that use database prefixes. Some call it a security feature. Some call it a way to have multiple installations in one database.

The main pro is that it's harder to guess the whole table name. On the other hand, if you have some kind of access to the database I would say you could figure out the scheme/table list.

Is it a viable security feature?

  • 55
    Security by obscurity is never real security. – BadSkillz Apr 5 '16 at 8:56
  • 3
    Only more secure if your only attack vector on the database is your child's name... – Michael Apr 5 '16 at 16:09
  • 5
    I'm just guessing but I think convention of database prefix started because of shared hosting. Many web host offer only 1 DB on cheaper plans. Having multiple site use same DB by prefixing table is the only way to get across this limitation. – VarunAgw Apr 5 '16 at 17:35
70

This is security through obscurity. While it might not hurt you in all cases, on it's own it does not provide viable security. Do not rely on this as your protection.

A short parable

Let's say you keep your money in a jar labeled MONEY in your house. Since you know you sometimes forget to lock the door when you leave the house, you relabel the jar COOKIESto prevent a thief from finding it. Would you feel more secure now? Sure, a very lazy, dumb thief might miss it, but you would not have to be a master thief to steal your money. Wouldn't it be better to just remember to lock the door instead?

Back to the computer world

Let's say you have an old phpBB installation with an SQL injection vulnerability. By default the tables are prefixed by phpbb_. You change this to obscure_. Will this help you?

A naive scan might fail to find hard coded table names (like phpbb_users with all the passwords), and therefore fail. But even a script kiddie could run a script that runs a SHOW TABLES and finds your obscure_users. In fact, there are tools that will automatically dump the contents of all your tables through a SQL injection vulnerability.

Conclusion

So what is the lesson here? While changing table prefixes (relabeling the jar) might perhaps protect you from the most basic dumb automated attack (the stupid, lazy thief), you would still be vulnerable to simple attacks performed by script kiddies (a thief searching through your jars). When you have a real vulnerability like SQL injection, the solution is to fix that vulnerability (lock the door), and not to add a thin veil of obscurity.

That said, a simple precaution that might slow down some attacks might still be worthwhile as "defence in depth" if it has no harmful side effects. Just don't feel safe just because you implement it.

(As an addendum, I should say that running multiple installations in the same database might come with security implications on it's own, depending on the situation.)

  • 10
    "While it might not hurt you in all cases" ... Why would this hurt you in any case? I see no reason why making a malicious user's work more difficult (no matter by how little) is a negative. I agree this is not security by the way, but it's wrong to discourage it. – James Apr 5 '16 at 13:33
  • 3
    @b3njamin I am talking about security through obscurity in general. It is not always harmful, but it can be sometimes. – Anders Apr 5 '16 at 13:37
  • 14
    @b3njamin Because it makes you feel safe while you are not. "Oh ship! It's so late! Where's my keys? Left pocket? Nope. Right one? Nope! Bed? Nope. Oh fudge it, nobody's gonna look into the cookie jar anyway". – Margaret Bloom Apr 5 '16 at 13:48
  • 4
    @b3njamin the only cases I can think it would hurt you are because of existing bad code. For example, I've seen people write wordpress plugins which make direct queries on wp_* tables without going through the framework's abstractions. You can say that's not the fault of the person obscuring the table prefixes, but it's a potential real-world problem. – moopet Apr 5 '16 at 15:59
  • 4
    ...if it has no harmful side effects... For table prefixes, I would consider the very slight decrease in readability enough to make it not worth it. You're buying pennies worth of security for dollars of clarity. – MGOwen Apr 6 '16 at 23:50
20

No, it's snake oil.

When someone finds an SQL injection vulnerability in an application, then not knowing the table names is only a very minor hurdle. Many stock injection payloads (like the good old universal password ' OR '1' = '1) do not even need to know any table name. And if the attacker discovers a way to dump data from arbitrary tables, they just have to dump the system-table INFORMATION_SCHEMA.TABLES (or equivalent, depending on used database system) and they get the names of all other tables.

It's not necessarily toxic snake oil, though. Being able to have multiple installations share the same database can be a legitimate feature (like on a low-budget web hoster which only allows you to use a single database on their MySQL cluster) and as long as you only have a random prefix and the table names are still human-readable it doesn't affect maintenance that much. But it doesn't add much security.

  • I agree with your snake oil remark and and ability to dump all the table names. But the idea of using an obscure prefix isn't to prevent SQL injection on the table in use. More so to try and combat SQL injection that uses another table other than the one in use. Either way, fruitless to fight if you have SQL injection introduced in the first place. – Bacon Brad Apr 5 '16 at 16:22
  • @BradMetcalf Then what is the idea of using obscure prefixes in your opinion? – Philipp Apr 5 '16 at 16:24
  • 1
    How this technique came about is CMSs. SQL injection would be used to retrieve and list results of a table that could help expose session information of admins so they can use that to gain entry. If you knew it was jos_sessions for Joomla for example you knew exactly what to target. So the idea is to allow the admin to change it to bacon_sessions to make this harder to attack. At least stop most drive by automated attacks. I personally only use this technique for CMSs purely for the automated attacks. Not all SQL attacks. Custom applications it really isn't worth implementing. – Bacon Brad Apr 5 '16 at 17:16
6

In widely adopted DBMS-based systems like WordPress, it's common practice to configure an instance to use table names like my_fav_prefix_posts in place of wp_posts.

Why? It purportedly slows down script kiddies.

A few years ago there was a spate of mass attacks on WordPress instances. Unsophisticated cybercriminals (script kiddies) used unsophisticated attack software (scripts) to touch large numbers of servers looking for vulnerabilities. When the scripts didn't find the wp_posts table they moved on to the next server in their attack list. Of course, a couple of weeks later the scripts became a little more sophisticated and WordPress instances could no longer hide their tables in plain sight.

I suppose that's the origin of the myth that alternative database prefixes give an extra layer of security. They don't.

  • 9
    "They don't." According to your anecdode they did, at least for a few weeks. Time to fix the security hole is better than no time. – kat0r Apr 5 '16 at 13:22
  • 1
    I absolutely agree that security through obscurity doesn't provide any real security, that it is not something one can rely on, that it must not be used as the only security layer and so on, but I think it can't be said, that it gives literally nothing. Why? Well, your anecdote. – Przemek Apr 5 '16 at 13:29
  • 1
    It purportedly slows down script kiddies Can you please add some references? I think the primary goal of this prefix is to allow multiple instances of WordPress in a single database. It also avoid conflicts if another CMS or software use a posts table. – A.L Apr 5 '16 at 15:04
1

I would say that this adds nothing to security. But if you cannot (or do not want to) rely on schemas to separate your tables among different applications, using different prefixes allow different applications to share same database without risk of conflict.

But is adds no real security.

  • 2
    Bingo. This feature is intended to allow multiple applications (or multiple instances of the same application) to share a single database. It was never meant to do anything for security. – duskwuff Apr 6 '16 at 5:26
  • 1
    This seems to duplicate the material in Philipp's earlier, more detailed answer. Rather than adding answers that duplicate existing material, we'd rather you find questions that don't already have good answers and answer them. – D.W. Apr 6 '16 at 20:28

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.