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 username jappleseed 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.