Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
6 Removed unnecessary content, fixed punctuation.
source | link

SQL injection -- but why isn't escape quotes safe anymore?

I haven't seen this question asked, and feel it's a valuable bit of information to have, especially because this is a huge source of mystery and confusion.

Raw SQL

When you're writing SQL -- for anything that takes human input really, a lot of things have been done to avoid the injection.

Everyone that's heard of sqlSQL injection knows that (I'm going to use PHP as a sample) doing something like this isn't safe:

$sql = "SELECT * FROM `users` 
.  WHERE `userName` = "{$_POST["username"]}" 
.  AND `pass` = "{$_POST["pass"]}";";

Magic

Then of course, someone came out with the idea of using "magic escape quotes" to deal with program input that wasn't sanitized correctly and directly put into sql as a result of bad practices. This didn't really solve the issue with SQL injection, but it did mean all user input got mangled up.

Adding slashes

So, some people turned off magic quotes. Then, they parsed user input before the point of SQL through addslashes() which in theory escapes all the quotes and your hacker can't do ' OR 1=1, but even the documentation for addslashes it's self say that you shouldn't use addslashes, it says use the database-specific function such as mysql_real_escape_string(), but this is still said to not be enough by some.

Adding slashes specific to Database

So, we can't use DBMS specific *_real_escape_string, we can't use add slashes, the "magic quotes" thing caused lots of issues, and the web is full of short worded quotes such as:

"A dedicated hacker will find a way to jump through your quote-escaping loops, just use the DBAL prepared statements" - John Q any programmer

Okay, so that scared me enough to use prepare statements and a DBAL. It didn't really explain anything, but it sounds good because I've heard it a lot.

Prepared statements

So now we're using PDO, or a DBAL from a framework, or something else that wraps all our sql and makes sure someone can't run an sql injection.

My question is basically a "why not?", not a "what should I use?". The web's full of people telling you to use this or use that or whatever, but no explanations of why these things had to happen.

Direct questions

Pointed questions (reminder, I'm asking about SQL, PHP was an example language because of it's bad rep around SQL, concepts are universal):

  1. Why can't we escape all user input using "magic" ?
  2. Why wasn't addslashes "good enough"?
  3. Whats wrong with using DB-specific escape functions, and why was it better than addslashes?
  4. Why are prepared statements with frameworks and PDO being hailed as the gold standard of SQL? Why are they better? Why can't I do an SQL injection with these, where as I COULD have with the previously mentioned means? Can a programmer not somehow manage to still screw this up? What should they look out for?
  5. Any other concerns I haven't brought up?

SQL injection -- but why isn't escape quotes safe anymore?

I haven't seen this question asked, and feel it's a valuable bit of information to have, especially because this is a huge source of mystery and confusion.

Raw SQL

When you're writing SQL -- for anything that takes human input really, a lot of things have been done to avoid the injection.

Everyone that's heard of sql injection knows that (I'm going to use PHP as a sample) doing something like this isn't safe:

$sql = "SELECT * FROM `users` 
.  WHERE `userName` = "{$_POST["username"]}" 
.  AND `pass` = "{$_POST["pass"]}";";

Magic

Then of course, someone came out with the idea of using "magic escape quotes" to deal with program input that wasn't sanitized correctly and directly put into sql as a result of bad practices. This didn't really solve the issue with SQL injection, but it did mean all user input got mangled up.

Adding slashes

So some people turned off magic quotes. Then they parsed user input before the point of SQL through addslashes() which in theory escapes all the quotes and your hacker can't do ' OR 1=1, but even the documentation for addslashes it's self say that you shouldn't use addslashes, it says use the database-specific function such as mysql_real_escape_string(), but this is still said to not be enough by some.

Adding slashes specific to Database

So, we can't use DBMS specific *_real_escape_string, we can't use add slashes, the "magic quotes" thing caused lots of issues, and the web is full of short worded quotes such as:

"A dedicated hacker will find a way to jump through your quote-escaping loops, just use the DBAL prepared statements" - John Q any programmer

Okay, so that scared me enough to use prepare statements and a DBAL. It didn't really explain anything, but it sounds good because I've heard it a lot.

Prepared statements

So now we're using PDO, or a DBAL from a framework, or something else that wraps all our sql and makes sure someone can't run an sql injection.

My question is basically a "why not?", not a "what should I use?". The web's full of people telling you to use this or use that or whatever, but no explanations of why these things had to happen.

Direct questions

Pointed questions (reminder, I'm asking about SQL, PHP was an example language because of it's bad rep around SQL, concepts are universal):

  1. Why can't we escape all user input using "magic" ?
  2. Why wasn't addslashes "good enough"?
  3. Whats wrong with using DB-specific escape functions, and why was it better than addslashes?
  4. Why are prepared statements with frameworks and PDO being hailed as the gold standard of SQL? Why are they better? Why can't I do an SQL injection with these, where as I COULD have with the previously mentioned means? Can a programmer not somehow manage to still screw this up? What should they look out for?
  5. Any other concerns I haven't brought up?

SQL injection -- why isn't escape quotes safe anymore?

Raw SQL

When you're writing SQL -- for anything that takes human input really, a lot of things have been done to avoid the injection.

Everyone that's heard of SQL injection knows that (I'm going to use PHP as a sample) doing something like this isn't safe:

$sql = "SELECT * FROM `users` 
.  WHERE `userName` = "{$_POST["username"]}" 
.  AND `pass` = "{$_POST["pass"]}";";

Magic

Then of course, someone came out with the idea of using "magic escape quotes" to deal with program input that wasn't sanitized correctly and directly put into sql as a result of bad practices. This didn't really solve the issue with SQL injection, but it did mean all user input got mangled up.

Adding slashes

So, some people turned off magic quotes. Then, they parsed user input before the point of SQL through addslashes() which in theory escapes all the quotes and your hacker can't do ' OR 1=1, but even the documentation for addslashes it's self say that you shouldn't use addslashes, it says use the database-specific function such as mysql_real_escape_string(), but this is still said to not be enough by some.

Adding slashes specific to Database

So, we can't use DBMS specific *_real_escape_string, we can't use add slashes, the "magic quotes" thing caused lots of issues, and the web is full of short worded quotes such as:

"A dedicated hacker will find a way to jump through your quote-escaping loops, just use the DBAL prepared statements" - John Q any programmer

Okay, so that scared me enough to use prepare statements and a DBAL. It didn't really explain anything, but it sounds good because I've heard it a lot.

Prepared statements

So now we're using PDO, or a DBAL from a framework, or something else that wraps all our sql and makes sure someone can't run an sql injection.

My question is basically a "why not?", not a "what should I use?". The web's full of people telling you to use this or use that or whatever, but no explanations of why these things had to happen.

Direct questions

Pointed questions (reminder, I'm asking about SQL, PHP was an example language because of it's bad rep around SQL, concepts are universal):

  1. Why can't we escape all user input using "magic" ?
  2. Why wasn't addslashes "good enough"?
  3. Whats wrong with using DB-specific escape functions, and why was it better than addslashes?
  4. Why are prepared statements with frameworks and PDO being hailed as the gold standard of SQL? Why are they better? Why can't I do an SQL injection with these, where as I COULD have with the previously mentioned means? Can a programmer not somehow manage to still screw this up? What should they look out for?
  5. Any other concerns I haven't brought up?
5 edited tags
| link
4 edited tags
| link
3 edited tags
| link
    Tweeted twitter.com/#!/StackSecurity/status/66688511742455808
2 added 1 characters in body; edited body
source | link
1
source | link