A well-known, often-publicized type of vulnerability is SQL injection. Many web sites and applications perform database lookups based on user input, without checking that input, which is what SQL injection attacks take advantage of.
Many people think that using stored procedures prevents SQL injection, but they don’t fully prevent it. Stored procedures can help, but they do not solve some classes of bugs. For example, they prevent the use of queries like this xyzzy’ or 1=1 but not attacks that manipulate the database or the schema, such as “xyzzy); drop table sometable”.
The best way to prevent SQL injection vulnerabilities is to use what are often referred to as parameterized queries or placeholder queries. In short, you do not use string concatenation to build such queries; rather, you use the database connection and query library functionality to build the SQL statement. So, instead of the pseudocode we’ve been using, like this:
String query = “select * from table where id=” + x;
we would instead use something like this pseudocode:
String query = “select * from table where id=?”
SQLBindParameter(query,1,x);
This treats x as the parameter to query for, so if an attacker inserts 98006′ or 1=1 –, the database will search for a postal code that matches “98006′ or 1=1 –” and will discover that it does not exist, instead of just appending it to the end of the SQL query.