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

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.