SQL statements in code should always be written using binding. There are a very few edge cases where this is not possible: if you encounter one, you need to re-think your approach so that you don’t need to use that edge case (e.g. perhaps a stored proc or trigger is more appropriate).
SQL statements in Python
Wrong way:
curs = connection.cursor()
curs.execute("SELECT col1, col2 FROM atable WHERE id = " + id_var)
curs.execute("""
UPDATE atable SET col1 = %s
WHERE id = %d" % (new_value, id_var))
""")
Both statements make SQL injection easy. What if idvar or newvalue were ; SELECT * FROM atable ; --?
The first .execute uses string concatenation, the second uses string formatting to accomplish the same goal.
Right way:
curs = connection.cursor()
curs.execute(
"SELECT col1, col2 FROM atable WHERE id = ?",
(id_var)
)
curs.execute(
"UPDATE atable SET col1 = ? WHERE id = ?",
(new_value, id_var)
)
This way, the database driver uses binding to pass the values where the ‘?’ characters are in the statement. The driver takes care of checking for safety and proper datatypes, quoting and escaping characters as needed. This entirely avoids SQL injection.
SQL statements in Perl
Wrong way:
$sth = $dbh->prepare(”SELECT col1, col2 FROM atable where id = $id_var”);
$sth->execute();
Avoid variable substitution, concatenation, and sprintf.
Right way:
$sth = $dbh->prepare('SELECT col1, col2 FROM atable where id = ?');
$sth->execute( $id_var );
Use the ‘?’ prototype in the prepare call, then execute with the variables, in order, to bind to the prototypes.