SQL has security issues beyond the typical user and permissions already discussed. It is critically important that any front end to a SQL database be secure. This normally means preventing users from committing SQL injection attacks against a database. In a SQL injection attack a malicious user 'injects' injurious SQL code into a SQL statement. These attacks can be particularly subtle, but are fairly easily defeated.
Injection attacks are successful when input is not properly filtered. Imagine a simple user authentication system that checks to see whether a call to a user table returns at least one record. The Python code that defines the statement looks like:
sql = f"SELECT * FROM users WHERE uid = '{uid}'"
The uid variable comes from a simple web form. Imagine that
the attacker enters the following value in the uid field of the form:
whatever' OR 'x'='x
The resulting SQL statement as generated by Python is:
SELECT * FROM users WHERE uid = 'whatever' OR 'x'='x'
Since 'x' is always equal to 'x' this would dump the entire contents of
the user table.
Most database servers allow multiple SQL statements to be executed when they are separated by ';'. This can also be used in a SQL injection attack. Using the same Python code as before the attacker enters:
whatever'; DELETE * FROM users; SET x = 'x
The resulting SQL statement(s) as generated by Python is:
SELECT * FROM users WHERE uid = 'whatever';
DELETE * FROM users;
SET x = 'x'
The attacker is not logged in, but the entire contents of the 'users' table could be deleted.
If data types are not checked then similar attacks can be made. The Python code:
sql = f"SELECT * FROM broad WHERE broadId = {id}"
clearly expects id to be an integer variable. Instead, the
attacker enters:
0; DROP TABLE broad;
creating the SQL statement:
SELECT * FROM broad WHERE broadId = 0;
DROP TABLE broad;
potentially destroying the 'broad' table.
SQL injection attacks can be easily defeated by parameterizing any input that is passed to a SQL statement. Parameterizing is automatically done by statement preparation. Thus the first Python example would be written as:
sql = "SELECT * FROM users WHERE uid = %(uid)s"
params = {'uid': uid}
cursor.execute(sql, params)
The %(uid)s is a placeholder, and the params
variable is a dictionary that contains the values that replace the
placeholder(s). The execute method filters, escapes, and
quotes the replacement data for proper use in the prepared statement. Thus
the attempted attack input:
whatever' OR 'x'='x
becomes just a string that happens to contain escaped (i.e. \')
single quote characters:
SELECT * FROM users WHERE uid = 'whatever\' OR \'x\'=\'x'
which executes with errors, but returns an empty result set.
The attempt to drop the table becomes:
SELECT * FROM users WHERE uid = '0; DROP TABLE broad;'
with the uid parameter being just a simple single-quoted
string.
Note that different languages, drivers, APIs, and extensions provide different methods for filtering and escaping input, but almost all provide some kind of parameterizing specifically to deal with SQl Injection.
Now you will understand why this cartoon is funny.