What must be escaped in SQL?

Question

When using SQL in conjunction with another language what data must be escaped? I was just reading the question here and it was my understanding that only data from the user must be escaped.

Also must all SQL statements be escaped? e.g. INSERT, UPDATE and SELECT

What programming language/environment are you using (other than SQL)? — Matthew Flaschen, May 18 '12 at 00:26
Different languages have support for various mechanisms of dealing with SQL injection. One of the best is prepared statements. I believe ColdFusion uses cfquery and cfqueryparam for prepared statements. PHP has [PDO prepared statements](http://php.net/manual/en/pdo.prepared-statements.php). — Matthew Flaschen, May 18 '12 at 00:36

score 1 · Accepted Answer · answered May 18 '12 at 00:27

1

EVERY type of query in SQL must be properly escaped. And not only "user" data. It's entirely possible to inject YOURSELF if you're not careful.

e.g. in pseudo-code:

$name = sql_get_query("SELECT lastname FROM sometable");
sql_query("INSERT INTO othertable (badguy) VALUES ('$name')");

That data never touched the 'user', it was never submitted by the user, but it's still a vulnerability - consider what happens if the user's last name is O'Brien.

answered May 18 '12 at 00:27

Marc B

356,200
43
426
500

This is a reflected SQL injection attack by the [O'Brien dynasty](http://en.wikipedia.org/wiki/O%27Brien_dynasty). – Matthew Flaschen May 18 '12 at 00:34

score 1 · Answer 2 · answered May 18 '12 at 00:37

1

Most programming languages provide code for connecting to databases in a uniform way (for example JDBC in Java and DBI in Perl). These provide automatic techniques for doing any necessary escaping using Prepared Statements.

answered May 18 '12 at 00:37

Adrian Pronk

13,486
7
36
60

Except some databases support true prepared statements, which offer the same security benefits but can also have better performance. Usually, the main library (JDBC, DBI, PDO) does not make this distinction visible to the end-user. – Matthew Flaschen May 18 '12 at 00:39
That's true: I've often used wireshark to sniff my SQL queries to discover megabytes of SQL being sent to the server while processing a loop because the client driver did all the Prepared-Statement handling itself and sent the fully expanded SQL with each request. – Adrian Pronk May 18 '12 at 00:52

domvoyt · Answer 3 · 2012-05-18T02:43:57.693

All SQL queries should be properly sanitized and there are various ways of doing it. You need to prevent the user from trying to exploit your code using SQL Injection. Injections can be made in various ways, for example through user input, server variables and cookie modifications.

Given a query like:

"SELECT * FROM tablename WHERE username= <user input> "

If the user input is not escaped, the user could do something like

' or '1'='1

Executing the query with this input will actually make it always true, possibly exposing sensitive data to the attacker. But there are many other, much worse scenarios injection can be used for.

You should take a look at the OWASP SQL Injection Guide. They have a nice overview of how to prevent those situations and various ways of dealing with it.

score 0 · Answer 4 · answered May 18 '12 at 01:23

I also think it largely depends on what you consider 'user data' to be or indeed orignate from. I personally consider user data as data available (even if this is only through exploitations) in the public domain, i.e. can be changed by 'a' user even if it's not 'the' user.

Marc B makes a good point however that in certain circumstances you may dirty your own data, so I guess it's always better to be safer than sorry in regards to sql data.

I would note that in regards to direct user input (i.e. from web forms, etc) you should always have an additional layer server side validation before the data even gets near a sql query.

What must be escaped in SQL?

4 Answers4