22

I just met a developer who prepended every table and column name in his MYSQL databases with an underscore (e.g. _users, _name, _active). When I questioned the practice he stated that this helps prevent SQL injection attacks - I have never come across this practice/advice before. How does it help to prevent SQL injection attacks?

yoozer8
  • 7,361
  • 7
  • 58
  • 93
foiseworth
  • 951
  • 2
  • 11
  • 19
  • The only thing I could think it would help against is if the attacker is attempting to guess the table names. But with proper sanitation of database inputs, it should be completely unnecessary. All it will do is confuse your devs. – Travesty3 Nov 19 '12 at 16:59
  • 4
    Hopefully you'll share this page with him. – MikeSmithDev Nov 19 '12 at 17:02
  • 4
    Paranoid Dev? Possibly, possibly not. Incompetant Dev? Very likely. – MatBailie Nov 19 '12 at 17:04
  • 1
    Seems like a "clever" solution. And that's what it is IMHO: "clever", making development harder, and not actually solving the problem. AS @AndyLester says, a hacker could still call system tables (or standardly named schema views, and get all your table names anyway!). – sebt Nov 19 '12 at 17:47
  • 2
    Please don't rely on sanitization of input. The safest solution is to not introduce unsafe data to your SQL commands, and to use parametrized queries, always, every time. http://bobby-tables.com/ has examples. – Andy Lester Nov 19 '12 at 19:20

2 Answers2

29

No.

His thinking goes "If the attacker doesn't know the name of my tables, then the attacker can't mess with them." However, you're still vulnerable to SQL injection, and the attacker can still cause arbitrary system calls, perhaps to well-known system tables. What if he adds some SQL code that causes very long queries against a system table that bogs down your server?

Security through obscurity is no security at all.

Andy Lester
  • 91,102
  • 13
  • 100
  • 152
  • 6
    +1 for mentioning system tables. If the attacker doesn't get to know what your puppy's name is, but can delete or corrupt your whole database, does it matter? (Ok, for sensitive info, like bank card info, it might...). Not to mention, by querying the metadata, one can easily decipher this _clever_ scheme... – ppeterka Nov 19 '12 at 17:01
  • Thanks for the clarification Andy (and the apt quote). I hadn't thought of system tables! – foiseworth Nov 20 '12 at 17:20
  • It's also worth pointing out that renaming the tables from `user` to `_user` is not a very difficult change for an attacker to try, especially since the attacker is probably a program, not a human. It makes about as much sense as changing your password from `password` to `passw0rd` and thinking "No attacker will know!" – Andy Lester Nov 21 '12 at 16:49
4

_this, _ here has no relation to security ,with that it can be vulnerable

Good read

Best way to prevent SQL injection?

Community
  • 1
  • 1
NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143