Is using a DB prefix for tables more secure?

Question

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?

Only more secure if your only attack vector on the database is your child's name... — Michael, Apr 05 '16 at 16:09
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. — , Apr 05 '16 at 17:35

Anders · Accepted Answer · 2016-04-05T13:49:33.450

69

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.)

edited Apr 05 '16 at 13:49

answered Apr 05 '16 at 09:09

Anders

65,582
24
185
221

Good balance in your answer! The bottom line is building in resistance to low skill attackers (the dumb thief) is defense in depth, and if it's a simple process with no other side effects then it is hard to argue it's not worthwhile. – Jeff Meden Apr 05 '16 at 13:06
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 05 '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 05 '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 05 '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 05 '16 at 15:59
@MargaretBloom Agreed, if this practice makes people 100% safe to the point they set their admin password to 'abc123' then it is harmful, but it's unlikely most of those who take advantage of it do not fall into that category. – James Apr 05 '16 at 16:24
I'm more likely to steal your cookies than your money. – Joshua Apr 05 '16 at 23:03
1

@b3njamin One reason the OP cites is that it enables having multiple instances of the same application in the database. In this case, you would need multiple database user accounts and extremely strict (and cumbersome) access permissions to keep them isolated. For an example, say you fix a SQL injection attack, but because you have a lot of clients, it takes time to upgrade all of the instances. What happens if one of them gets breached in the mean time? In an environment where some developers claim this is a security feature, I'm not inclined to trust that they set up all those users. ;) – jpmc26 Apr 06 '16 at 03:12
1

@moopet I've seen this. And to me it's an instant delete. It says enough about the plugins quality. I don't want that. – janw Apr 06 '16 at 07:44
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 06 '16 at 23:50
@James "making a malicious user's work more difficult (no matter by how little) is a negative" 1) It can have a non trivial implementation cost. The gain can be so minuscule, and will not even be measurable for a targeted attack by a competent attacker. 2) Anything that involves changing software can introduce bugs, and anything that change security relevant code like a server could introduce security vulnerabilities. The risk may be very small for every conceptually simple code change, but changing a lot of lines of code multiplies the risk. – curiousguy Jun 22 '18 at 02:16
Making the malicious user's work more difficult sometimes correlates with making the honest user's work more difficult (with poor security measures, the difficulty might be increased by an equal amount). The code might be slightly less readable. The attention might be moved away from the logic of the program to annoying stuff that looks like noise in the code. The people doing code review might lose focus and skip something important.

curiousguy

Jun 22 '18 at 02:20

Philipp · Answer 2 · 2016-04-05T09:10:36.723

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.

edited Apr 05 '16 at 09:10

answered Apr 05 '16 at 09:03

Philipp

49,384
8
129
160

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 05 '16 at 16:22
@BradMetcalf Then what is the idea of using obscure prefixes in your opinion? – Philipp Apr 05 '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 05 '16 at 17:16

score 6 · Answer 3 · answered Apr 05 '16 at 11:14

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.

answered Apr 05 '16 at 11:14

O. Jones

379
1
5

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 05 '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 05 '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 05 '16 at 15:04

score 1 · Answer 4 · answered Apr 05 '16 at 16:02

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.

answered Apr 05 '16 at 16:02

Serge Ballesta

26,693
4
44
89

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. – Apr 06 '16 at 05: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 06 '16 at 20:28

Is using a DB prefix for tables more secure?

4 Answers4

Linked

Related