Although not as common as it used to be, SQL [‘see-kwell’] Injection (SQLi) vulnerabilities are still found in contemporary applications, particularly in “legacy” websites and code. A SQL Injection (SQLi) vulnerability refers to the ability of an attacker to craft a malicious input that will break the data/code barrier when the application attempts to use that data in a database command.
SQL Injection (SQLi) vulnerabilities can be some of the most serious security flaws in an application, potentially leading to unintended data exposure, loss of data integrity, and even loss of control of the database hosting server itself.
What Is SQL ?
To understand SQL Injection, you must first have some understanding of Structured Query Language (SQL), the protocol used to communicate with relational databases. Applications use SQL to insert, update, retrieve, and delete information in databases as a means of persisting and maintaining the data managed by the application.
Conceptually (and most-simply), information in a relational database is organized into discrete tables, each of which represents a relevant entity in the real-world being modeled by the application such as employees, widgets, or wombats, etc. Each entry in the table (called a “row”) represents a specific instance of that “type” of entity. The “columns” of a table each represents an relevant attribute of the corresponding entity. The table of “Employees” might have an “employeeID” column, whereas the table of “Widgets” might feature a “partNumber” column.
SQL provides a means to interact syntactically with a database to create a manage the tables, as well as to create and manage the rows of each table.
Although this severely over-simplified description is sufficient for our needs in this article, it is important to remember that modern relational databases are full-fledged programming and computing platforms, providing support for users, permissions, functions, and access to the hosting operating system. This point is critical to understanding both the potential severity of SQL Injection (SQLi) vulnerabilities as well as strategies to mitigate them.
SQL Injection Example: Authentication Bypass
The classic example of a SQL Injection vulnerability (apologies for not re-inventing the wheel) involves authentication, that is, checking to see whether a given account identifier and password exist in the database.
Consider the following table definition in which each row represents an existing account used to access an application. Note that the password field is designed to be stored as the product of a hashing algorithm, to discourage the transfer of login credentials in clear text.
CREATE TABLE IF NOT EXISTS CaseStudy_Accounts ( AccountId varchar(128) NOT NULL PRIMARY KEY, HashedPW varbinary (128) NOT NULL, # 512 bits Balance decimal NOT NULL DEFAULT 0.0, Comments varchar(1024) );
Let’s continue our scenario by assuming that our application requires users to authenticate using the following window:
| Account: | JohnSmith |
| Password: | JohnsPassword |
| OK | |
It would be reasonable for the application to simply check to see if the given account identifier and password (hash) appear in the Accounts table as means of authentication. To that end, let’s imagine that when the user clicks “OK” the form is submitted to the application via HTTPS where the following SQL statement is constructed using the “account” and “password” parameters from the login form:
String selectStmt =
"SELECT * FROM CaseStudy_Accounts WHERE AccountId='" +
accountId + "' AND SHA2 (" + "'" + password + "'" +
", 512) = HashedPW;" ;
In a normal use-case, a user might enter NAME as their name and PW as their password, producing the following SQL statement, that when applied to the database , returns a record only if the given name and hashed password exist as a row within the Accounts table:
SELECT * FROM CaseStudy_Accounts
WHERE AccountId='NAME' AND SHA2('PW', 512) = HashedPW;
Of course, if the application fails to properly validate inputs, it is possible that a malicious user might enter a value specifically crafted to pervert the resulting SQL statement. For example:
| Account: | ‘ OR 1=1; — |
| Password: | anything |
| OK | |
which would produce the following SQL statement:
SELECT * FROM CaseStudy_Accounts
WHERE AccountId='' or 1=1; -- ' AND SHA2 ('anything', 512) = HashedPW;
Look carefully at what this statement actually does. The logic of the query has been changed by the malicious input as follows:
- The accountId being searched for is now the empty string
- Any row of the Accounts table will match because “OR 1=1” will always be true
- The semi-colon terminates the SQL statement
- The — syntax declares the remainder of the line to be a comment
Or, in simplified form:
SELECT * FROM CaseStudy_Accounts WHERE AccountId=''or 1=1;
In other words, the malicious input has perverted the query in a manner that will cause the query to match and return every row of the Accounts table. If the application considers >0 rows to be success, then the submission will be considered “authenticated” — even without a password (unless the user interface requires one).
Variations of SQL Injection (SQLi)
It is sometimes useful to distinguish the degree to which a SQL Injection (SQLi) vulnerability can be leveraged. In some cases, it may only be possible to broaden the scope the original SQL statement and gain unintended access to information. In other cases, it may be possible to specify and execute arbitrary SQL statements, clearly a nightmare scenario. Sometimes it is not obvious by observing the behavior of the application, whether inserted SQL statements are actually being executed by the database. This is called “blind SQL Injection” as the execution of inserted SQL statement must be inferred rather than observed.
Severity of SQL Injection (SQLi) Vulnerabilities
There are several contributing factors that lead to SQL Injection vulnerabilities within applications. In addition, a vulnerability may be more or less severe depending on other factors. Designers should seek to minimize the occurrence or degree of each of the following:
- The use of Dynamic SQL: The root cause of SQL Injection (SQLi) is the splicing together of strings, including untrusted inputs, to form the SQL statements which are subsequently provided to the database for execution.
- The privilege of the application account being used within the database: The more power the database account has that is operating on behalf of the application, the greater the potential damage from SQL Injection (SQLi) exploits.
- The failure to properly validate untrusted input to the application: It is sometimes possible to “validate your way out of trouble” by rejecting any characters or inputs that do not meet the acceptance criteria for a particular field.
- Logic that does not fail securely. In our Authentication example, one of the reasons the SQL Injection (SQLi) vulnerability could be exploited as an Authentication Bypass was that the application treated the SQL query as a boolean predicate: it either returned nothing implying authentication failure, or something implying authentication success. It was written in this manner despite the fact that each accountID entry is required to be unique, meaning that the authentication test should NEVER return multiple rows.
Myths About SQL Injection (SQLi)
| SQL Injection (SQLi) Myth | Reality |
| Using Stored Procedures will prevent SQL Injection (SQL) | Although there is some value provided by the type-checking of parameters, it is quite possible for SQL Injection to exist within stored procedures. |
| Escaping single quotes in untrusted input will avoid the problem | Not all injectable content, such as function calls contain single quotes. |
| I do not need to worry about data already in my database | This may or may not be true depending on the unlikely possibility that all data in the database has previously been validated with the same criteria used by the application. |
| I am using a database access layer/library, so it is not my concern | If the functions you are using utilize Dynamic SQL, you are at risk. It is the developer’s responsibility. |
| Using Prepared (Parameterized) Statements will avoid all SQL Injection (SQLi) issues | Prepared statements do not allow parameters in all positions in which developers may want to inject a value. |
| NoSQL databases are immune to SQL Injection | Technically true because NoSQL databases don’t speak SQL, but false in that malicious inputs can still pervert queries |
For insights into detecting SQL Injection (SQLi) vulnerabilities, see the article entitled: “How To Test For SQL Injection (SQLi)“.
For strategies on preventing SQL Injection (SQLi), see the article entitled “How to Prevent SQL Injection (SQLi)“.
About Affinity IT Security
We hope you found this article to be useful. Affinity IT Security is available to help you with your security testing and train your developers and testers. In fact, we train developers and IT staff how to hack applications and networks.
Perhaps it was a network scan or website vulnerability test that brought you here. If so, you are likely researching how to find, fix, or avoid a particular vulnerability. We urge you to be proactive and ensure that key individuals in your organization understand not only this issue, but also are more broadly aware of application security.
Contact us to learn how to better protect your enterprise.
Although every effort has been made to provide the most useful and highest quality information, it is unfortunate but inevitable that some errors, omissions, and typographical mistakes will appear in these articles. Consequently, Affinity IT Security will not be responsible for any loss or damages resulting directly or indirectly from any error, misunderstanding, software defect, example, or misuse of any content herein.