Thinkful SQL Response

thinkful_sql.md

SQL

Hi student,

SQL Injection is definitely a security flaw you'll always want to keep in mind when writing web applications that use SQL databases. SQL injection occurs when SQL queries are performed using standard string interpolation. Any language that supports SQL should also provide SQL libraries that provide methods to safely construct and execute SQL queries using some type of parameter sanitation method or parameter binding.

SQL Injection Examples

First, I'll show some examples of SQL injection in practice.

Note: My examples are inspired by these examples.

SELECT fieldlist
  FROM users
 WHERE email = '$EMAIL';

If your code is directly connected and executing raw SQL queries, you're completely vulnerable. With this raw SQL query, a user can insert any valid SQL code and it will be executed on our database. They could try something like anything' OR 'role'='admin. Notice the lack of the opening quote in anything' and the lack of a close quote on 'admin. This would be inserted as:

SELECT fieldlist
  FROM users
 WHERE email = 'anything' OR 'role'='admin';

And now they've accessed all fields of admin users in the USERS table.

They could also do the infamous anything'; DROP TABLE members; --

SELECT fieldlist
  FROM users
 WHERE email = 'anything'; DROP TABLE users; --';

You can see that following the inserted command with ; -- will terminate our user's command, and comment out the rest of the code's execution. And now our whole users table is gone! Hopefully you have a backup.

Sanitizing

SQL injection was a common problem in the early days of the web. These days, simply using the SQL libraries for your language should provide you with the security needed to avoid SQL injection attacks.

The most common syntax is replacing provided parameters with ?s. The following example is pseudo code, but is relatively the same concept across all languages.

email = 'null@void.com'; // provided through web form

// prepare our query using the sql library
query = db.prepare("SELECT email, userid FROM members WHERE email = ?;");

// safely replace the `?` with our provided email parameter and execute the query
user = query.execute(email);

---

I hope this helps. Please let me know if you need further help! Good luck!