Client Explanations: SQL Injection
SQL Injection is one of the most common website security vulnerabilities in which the programmer carelessly applies user input to a database command.
For example, a vulnerable website for Super Big Data Innovators allows users to
view profile pages for its corporate staff. The web address
http://superbigdatainnovators.com/profiles/jappleseed
is supposed to show the
profile for it’s CEO Johnny Appleseed. The profiles for its other officers are
similarly available by replacing jappleseed
with their username.
The implementation of this page performs a query against the content
database to the effect of Get me the user data for user _____
where the blank
would be filled in from the URL. For example, on the page /profiles/jappleseed
,
the blank would be jappleseed
, for /profiles/mmouse
, it would be mmouse
,
etc.
But what if a mischievous user typed in the URL /profile/jappleseed and then delete the users table
?
If the website programmer did not account for this, the command to the database
would be Get me the user data for user jappleseed and then delete the users table
. The database has no way of knowing that this was not the intended
command: it just does as its told.
Consequences
- SQL injection can lead to the disclosure of sensitive data including user profile data, passwords or other PII.
- Privileged financial or proprietary information can be leaked to unauthorized users
- Malicious users can damage the database by performing mass delete operations.
Mitigation
- Use SQL Parameters. Most databases allow the query to separate command from
parameter. For example,
Get me the user data for user $1. Parameters: $1 -> "jappleseed"
. - Use proper and wrapping of data. If this was used, the database command would
be
Get me the user profile for user 'jappleseed'
. If the prankster sends extra data, it would sill be contained inside the quote:Get me the user profile for user 'jappleseed and delete the products table'
. The database would not find a user with usernamejappleseed and delete the products table
and so the page would report a not found error. Database APIs provide functions to properly wrap data so that even if the bad data contains a'
character, the query would still be safe. - Secondarily, configure database permissions so the web server only has access to the data it needs. For example, it needs read-only access to the products table but will need to write changes to the user table when users change their passwords or profile information. This should not be the primary mitigation technique, but is a nice extra.
- In a vulnerable site, every single query must be manually audited by a qualified software developer.