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 string generated by Python is:
SELECT * FROM users WHERE uid = 'whatever' OR 'x'='x'
Since 'x' is always equal to 'x' this dumps 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) string generated by Python are:
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 is 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 are 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.
A complementary approach to database security is to set the permissions
on individual tables to disallow deletion or updating for particular
users. So imagine that the web front end that accesses the broad
table is set up so that the DBA user web is the use
allowed to access the database. web is defined to have
read-only (i.e. SELECT access only) to the broad
table. Thus, even if a deletion command got through, it would not be
allowed to execute a DELETE command.
The web front end for the DCRIS database on the course website has a
user that is allowed only SELECT access to all tables in
the DCRIS schema. You may enter deletion or updating code into the web
front end, but it is not executed because of the permissions on the
user.
Using both of these approaches is useful and appropriate.
Now you will understand why this cartoon is funny.