-2

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.

Community
  • 1
  • 1
Tim
  • 1
  • 141
  • 372
  • 590
  • 1
    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 Jun 08 '18 at 17:59

1 Answers1

3

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.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Thanks. (1) "once those queries are sent to the database (without the specific parameter values), the database saves them." Does the DBMS save the compiled queries in the form of stored procedures on the DBMS server? (2) "If you didn't use compiled query, but just decided to concatenate the SQL as a string", how is that done step by step? Do I need to create a stored procedure on the DBMS server, and the stored procedure accepts an argument from user and appends the argument to the end of the query? – Tim Jun 08 '18 at 18:35
  • You don't really see under the hood of the database. For example in JDBC, you prepare the query using something like `st = connection.prepareStatement("select * from applicant where name = ?")`. Then you run (many) times using `st.setString('Mary'); st.executeQuery();` or other parameter. – The Impaler Jun 08 '18 at 18:38
  • Thanks. About (2), can it be done directly in SQL client/server without using other programming language and driver such as JDBC? If yes, how would you do it step by step? – Tim Jun 08 '18 at 18:44