Security – Why SQL Injection Prevention Uses Parameterized Queries

hackinghistorySecuritysqlsql-injection

The way I see it, SQL injection attacks can be prevented by:

  1. Carefully screening, filtering, encoding input (before insertion into SQL)
  2. Using prepared statements / parameterized queries

I suppose that there are pros and cons for each, but why did #2 take off and become considered to be more or less the de facto way to prevent injection attacks? Is it just safer and less prone to error or were there other factors?

As I understand, if #1 is used properly and all caveats are taken care of, it can be just as effective as #2.

Sanitizing, Filtering, and Encoding

There was some confusion on my part between what sanitizing, filtering, and encoding meant. I'll say that for my purposes, all of the above can be considered for option 1. In this case I understand that sanitizing and filtering have the potential to modify or discard input data, while encoding preserves data as-is, but encodes it properly to avoid injection attacks. I believe that escaping data can be considered as a way of encoding it.

Parameterized Queries vs Encoding Library

There are answers where concepts of parameterized queries and encoding libraries that are treated interchangeably. Correct me if I'm wrong, but I am under impression that they are different.

My understanding is that encoding libraries, no matter how good they are always have the potential to modify SQL "Program", because they are making changes to the SQL itself, before it is sent off to the RDBMS.

Parameterized queries on the other hand, send the SQL program to the RDBMS, which then optimizes the query, defines the query execution plan, selects indexes that are to be used, etc., and then plug in the data, as the last step inside the RDBMS itself.

Encoding Library

  data -> (encoding library)
                  |
                  v
SQL -> (SQL + encoded data) -> RDBMS (execution plan defined) -> execute statement

Parameterized Query

                                               data
                                                 |
                                                 v
SQL -> RDBMS (query execution plan defined) -> data -> execute statement

Historal Significance

Some answers mention that historically, parameterized queries (PQ) were created for performance reasons, and before injection attacks that targeted encoding issues became popular. At some point it became apparent that PQ were also pretty effective against injection attacks. To keep with the spirit of my question, why did PQ remain the method of choice and why did it flourish above most other methods when it comes to preventing SQL injection attacks?

Best Answer

The problem is that #1 requires you effectively parse and interpret the entirety of the SQL variant you're working against so you know if it is doing something it shouldn't. And keep that code up to date as you update your database. Everywhere you accept input for your queries. And not screw it up.

So yes, that sort of thing would stop SQL injection attacks, but it is absurdly more costly to implement.

Related Topic