What are the four defensive ways to stop SQL injection attacks?

SQL injection is a kind of attack where an attacker sends malicious code that may be executed by the database server. The main cause for this kind of attack is a lack of context at the moment of execution. As a consequence, the database fails to distinguish data from your code.

Let’s go over four defensive ways you can stop SQL injection in your application.

Input validation

As the first line of defense against a SQL injection attack, input validation consists of enforcing strict constraints where possible.

For example, you can parse numeric identifiers into an integer for the early rejection of invalid input.

Note of caution:
To avoid interpersonal complications, your input validation mechanisms should not be too strict. I’m a developer from a French-speaking country, and we don’t have the same name rules as people in the US. An example of this would be enforcing that first and last names can only consist of letters in your app.
Learn more about people’s names here.

Prepared statement

Input validation alone is not enough for protection. Prepared statements with binding variables are an additional mechanism that can be combined with input validation.

Let’s consider the code below and see how we can improve it.

query = "SELECT id FROM users WHERE username='" + uname + "' AND password='" + pwd + "'"

The code above is vulnerable to SQL injection because we embed untrusted data (uname and pwd) in our SQL statement.

To fix the issue above, we need to use placeholdersalso called parameters or bind variables. You can use either anonymous positional placeholders, with ?, or named ones.

If we use anonymous placeholders, our code will look like this:

SELECT id FROM users WHERE username=? AND password=?

Next, you’ll need to bind parameters to the statement and execute the query.

Whitelist

Variable binding does not work for database tables and column names as these need to be specified upfront. To handle untrusted data in these locations, we need to use a whitelisted set of values.

Let’s see an example:

let myTable;

if(parameter === "user") myTable = "Users";
else if(parameter === "location") myTable = "Venues";
else throw new Exception("Bad input. Try again")

query = "SELECT * FROM" + table + "WHERE username LIKE ?"

Encoding

Encoding consists of rendering special charactersuntrusted data harmless. Unfortunately, in the context of SQL statements, using this technique is a bit complicated, as every database system has its own set of special characters.

Note: This solution should only be considered as a last resort.

Conclusion

SQL injection is one of the most popular attacks on the web. You must take the security of your apps seriously, especially when you’re using a DELETE or UPDATE statement in your code.

Remember:

1. You should never, ever trust the users. Always validate data they send to your app.
2. Use prepared statements as much as you can in your SQL queries when you have untrusted data.
3. Whitelist can be of help where you can’t use variable binding.
4. You should only think of encoding as a last resort to stop SQL injection in your app.