Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure. The SQL language is used to interact with that database information.
SQL injection refers to programming laziness when dealing with processing SQL allows clever attackers to manipulate HTML forms to “poison” SQL to subvert security measures and open up all your data to their scrutiny. There are few circumstances more terrifying than discovering your precious intellectual property, customer information including credit card details, and more are being perused by your competition or being sold on the dark web.
Herein we cover the basics of SQL injection, a huge topic with decades of background, to provide the first steps in understanding the importance of properly handling your SQL, the techniques used by your adversaries (even if you didn’t realize you had adversaries), and starting points to armoring your database.
The SQL injection attack works on “poisoning” dynamic SQL statements to cause a behavior other than what the programmers intended. A “dynamic statement” is one that’s generated at run-time using parameters passed in from a web form or URI query string.
The most common vector of attack for SQL injections are web forms. Following is a very simple web form which asks the user to input a username and password.
<form action=’login.php’ method="post"> <input type="username" name="username"/> <input type="password" name="password"/> <input type="submit" value="Submit"/> </form>
On the web page, this bit of HTML produces something like this:
The web page is termed the “front end” of the interaction; the processing of the SQL and the database access, the “back end.” Let’s examine some of these back-end SQL injections.
SQL Injection — Incorrectly Filtered Escape Characters
Web Forms As Attack Vector
The “back end” processing of the SQL and the database access — as opposed to the “front end” web page display of the web form — typically consists of a bit of SQL that’s something like this:
SELECT * FROM users WHERE username = $_POST['username'] AND password = $_POST['password'] ;
A typical SQL call to log into the web and access all that good data would look like:
SELECT * FROM users WHERE username = 'Typo Pumblechook' AND password = 'correct' ;
With one row successfully returned, the user is permitted access.
When an incorrect password is entered into the web form, the dynamic SQL becomes:
SELECT * FROM users WHERE username = 'Typo Pumblechook' AND password = 'wrong' ;
And the response becomes
0 lines returned
And the user is blocked from access.
A SQL injection attack happens when a malicious user enters a SQL fragment into the username web form element. Instead of the username Typo Pumblechook , consider the input
Typo Pumblechook' OR 1 = 1 LIMIT 1 -- ' ]. The attack consists of several points:
|OR 1 = 1||always evaluates to logical TRUE|
|LIMIT 1||forces just one row to be returned, which makes sense later|
|—||Is a comment delimiter, rendering the rest of the SQL unprocessed|
The dynamic SQL therefore becomes:
SELECT * FROM users WHERE username = 'Typo Pumblechook' OR 1 = 1 LIMIT 1 -- ' ] AND password = 'wrong' ;
And the response (remember, with an incorrect password supplied), becomes:
The password security check has been successfully bypassed and the user is permitted access. Woe to you!
If the LIMIT 1 clause is omitted the SQL becomes
SELECT * FROM users WHERE username = 'Typo Pumblechook' OR 1 = 1 -- ' ] AND password = 'wrong' ;
And the response (remember, with an incorrect password supplied), becomes:
|Thor the Canadian||absolutely|
That’s right, all the usernames and passwords are dumped into the lap of the hackers. Depending upon the circumstances around how the web page handles security all the keys to your kingdom have been handed over to bad actors. This, in a nutshell, is the SQL injection attack. More sophisticated attackers combine several techniques, such as the one above, to explore the gatekeeping vulnerabilities to gain access to the data, provide themselves with new logins which have elevated permissions, execute commands on the server that downloads and installs malicious programs, etc.
URLs As Attack Vector
It’s not only HTML forms that are popular SQL injection vectors, web browser URLs are also very frequently used because they’re just another gateway to the SQL that’s being consumed without validation. In this section, you’ll see how to leverage the vulnerability shown above through URLs.
Consider several web applications that share some information with users. These typically have URLs that point to a page that’s processed when a form’s Submit button is hit, arguments passed on the URL in a
name=value format, and some hidden processing to restrict items to be displayed in the same way (currently available items, products visible to the general public, etc.).
In both of these cases, the SQL that’s to be executed mirrors what was shown above, where the user inputs have not been sanitized because they’re trusted not to be malicious. Respectively, they’ll look something like:
SELECT * FROM products WHERE items = 'phones' AND public = 'true'
SELECT * FROM support WHERE docs = 'manuals' AND avail = 'true'
Notice the AND clause is common security where a company is preparing to publicly share a work in progress. It’s safely held internally, privately, until the database manager flips the state from ‘false’ to ‘true’, right? Wrong! Tweaking the URL manually to look like:
results in a now-malicious SQL query:
SELECT * FROM products WHERE items = 'phones'--' AND public = 'true'
and immediately you’re sharing all your phones, or all your manuals, stripping away your simple security attempt.
This is but one SQL injection violation using a URL as an attack vector. There are many, many others.
SQL Injection Remediation
The following best practices should be followed when hardening a database deployment against SQL injection attacks:
- Never trust user input: All user input must be validated and sanitized before any use in dynamic SQL statements.
- Procedurally use prepared statements: Build up SQL statements first, using user input as parameters, by prepared statements.
- Utilize stored procedures: Encapsulate and protect your SQL statements; stored procedures treat user input as parameters instead of raw, trusted input.
- Deploy regular expressions: Examine user input with regular expressions, search for and remove or disallow potentially malicious patterns.
- Enforce user access rights on database connections: The judicious allocation of user access rights restrict which accounts may connect to, and modify, the database, further sandboxing the reach of any malicious user input.
- Display error messages only to those who need to know: Error messages leak information vital to further attacks. Sharing the offending SQL statements and where errors occurred is exactly the sensitive information needed to refine attacks to known weaknesses in your particular database deployment. Share detailed information with administrators, using genteel, vague notifications like “An unexpected error has occurred. The support team has been notified. Please try again later.”
The non-profit Open Web Application Security Project (OWASP) provides cheat sheets for SQL injection prevention and query parameterization, and guides for software code review and testing SQL Injection vulnerabilities.
SQL injections are a class of attacks on your database(s), most commonly through HTML forms and web URLs. Injections take advantage of quick-and-dirty programming that trusts user input.
The workaround to SQL injection attacks are validating and sanitizing user inputs, using parameterized SQL statements, and other best practices (listed above).
Other best practices, such as sharing error message details only with administrators (and not with your determined attackers) are covered, and SQL injection mitigation and remediation resources from the Open Web Application Security Project are provided.
SQL Injection Sample Table Code
The following will build the code sample tables referred to in this blog post. Examine them interactively at SQL Fiddle.
CREATE TABLE users ( username VARCHAR(33), password VARCHAR(33) ) ; INSERT INTO users VALUES ( "Typo Pumblechook", "correct" ), ( "Pip Eva", "right" ), ( "Thor the Canadian", "absolutely" ) ;