By XKCD

If you ever run SQL queries to a remote database from your server, the number one thing you have to be careful about is making your queries impenetrable to SQL injection attacks.

SQL injection attacks are very commonplace and can be very harmful. An attacker could manage to drop entire tables or modify key information in your data.

Suppose you have any kind of code running on your server that needs to retrieve data from an external SQL-based database, or needs to insert new data into it. This means that when you write the SQL queries to do that, you’ll need to use local variables from your code so the program can run on its own. For a very simple example, suppose you want to insert today’s date into an external table. You obviously won’t write this query:

INSERT INTO table (date) VALUES(04/28/2017)

Instead, you will want a variable inside the parentheses after VALUES, so whenever the program executes, it’ll insert the correct date for that day. This is where things can get vulnerable. Take this example of an INSERT query in Python’s SQLite module:

sql = "INSERT INTO TABLE (date) VALUES ('%s')" % (date)cursor.execute(sql)

The code above is extremely vulnerable. What you want to do instead is use parameterized SQL queries. Example:

sql = "INSERT INTO TABLE (date) VALUES ('%s')"cursor.execute(sql, (date))

So in other words, you leave the variable’s placeholder in the query, but you pass the variable in the execute() method. You don’t ever pass the variable next to the query.

Facebooktwittergoogle_plusredditpinterestlinkedinmail