Why do Parameterized queries allow for moving user data out of string to be interpreted?

1
votes

From https://en.wikipedia.org/wiki/Code_injection#Preventing_problems

To prevent code injection problems, utilize secure input and output handling, such as:

  • Using APIs that, if used properly, are secure against all input characters. Parameterized queries (also known as "Compiled queries", "prepared statements", "bound variables") allows for moving user data out of string to be interpreted. Additionally Criteria API[7] and similar APIs move away from the concept of command strings to be created and interpreted.

I was wondering how and why "parameterized queries (also known as "Compiled queries", "prepared statements", "bound variables") allows for moving user data out of string to be interpreted" and prevent or mitigate code injection problems?

Can you also provide some examples in explanation?

Thanks.

1
sqlsql-injectioncode-injection
I would guess there are literally thousands of websites with in depth explanations and examples of sql injections, definitions of sql injections, and mitigation techniques for every db platform out there. You are asking for someone to provide an explanation on a topic that is heavily documented and only a google search away. – dfundako

1 Answers

3
votes

Compiled queries use special syntax that the database understands. They usually add placeholders for parameters such as in:

select * from applicant where name = ?

select * from applicant where name = :name

The exact syntax depends on the specific technology: JDBC, ODBC, etc.

Now, once those queries are sent to the database (without the specific parameter values), the database "saves" them. Later on (usually in the same database session), you can run them many times, by just providing the parameter values each time.

SQL Injection Safety

They are also safe against SQL injection. For example, if in the previous query instead of a simple value such as Mary you used the value x'; delete from applicant; -- the database will work safely. It would run something like:

select * from applicant where name = 'x; delete from applicant; --'

This query won't probably find anything and will be safe.

If instead you didn't use compiled query, but just decided to concatenate the SQL as a string you would do something like:

String sql = "select * from applicant where name = '" + param1 + "'";

And would end up with the UNSAFE query:

select * from applicant where name = 'x'; delete from applicant; --

This one would run two queries. The second one will delete all the information from your table. Probably not what you want.