The way I see it, SQL injection attacks can be prevented by:
- Carefully screening, filtering, encoding input (before insertion into SQL)
- 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?
