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.
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.
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 (
pwd) in our SQL statement.
To fix the issue above, we need to use
?, 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.
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 consists of rendering
Note: This solution should only be considered as a last resort.
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
UPDATE statement in your code.
View all Courses