SQL injection is when attackers manipulate a string which is used to construct an SQL query so that it returns unintended results.
- SQL injection is an attack that occurs when untrusted data is used to construct an SQL query. The data is inserted, or injected into the SQL query string, it allows an attacker to execute arbitrary requests to an SQL database, SQL injection sometimes called SQLI for short, is not the only form of code injection. But it is the most common. Most modern web applications rely heavily on databases, and by some estimates, 75% of databases use SQL. The same general principals we discuss for SQLI can be applied any time untrusted data is used to construct a value that's used for code or in a query. OWASP, the Open Web Application Security Project has consistently ranked code injection as the top security threat to web applications for the last 10 years. So this subject is definitely worth our attention. SQLI is easy for attackers to detect and to exploit. Let's look at an example of SQL Injection. Imagine a log in form, when the form is submitted, the application code constructs an SQL query to search for a matching username and password in the users table. Be sure to notice where the username variable is inserted into a string, and how it appears in the resulting SQL. Now imagine that a hacker visits the login form. Instead of providing a username the hacker submits a carefully crafted string in its place. Read the resulting SQL carefully, notice the single quote right after SQLI, when it's inserted into the string, that single quote closes the opening single quote in order to break out of the intended input value. The fragment or one equals one, well that will always return true, and the double dashes indicate the start of a comment in SQL. The result is that the query will match all users and the password clause would be ignored. Potentially this SQLI attack could bypass the login page, and grant access without the correct credentials. SQLI attacks can have many different goals. They can be used to probe the structure of a database, they can be used to steal data, this is probably the most common goal. To steal personal information, passwords, credit cards, or anything in the database that has value. They can add or change database data, perhaps to place an order or to elevate their access privileges. They may be used destructively, for truncating or dropping tables. Or as we saw in our example, it may be possible to trick an authentication test so that it evaluates to true without needing the correct credentials. A well known XKCD comic strip has fun with this idea, the school calls a mother, and says they're having computer trouble. They ask if she really named her son Robert single quote parenthesis semi colon drop table students semi colon dash dash? She says oh yes, little Bobby Tables we call him. His name has caused an SQL injection which erased database records at the school. The mom says and I hope you've learned to sanitize your database inputs. Any query which utilizes user input is vulnerable to SQLI, the most frequent area of attack are SQL where clauses, they're used to locate records which match parameters and they incorporate a lot of data from the current request. Other query types are equally vulnerable, just not as frequently used. Insert, update, and delete statements must be considered as well as other SQL clauses like select and order by. One variation on SQL injection is called blind SQL injection, a vulnerability may exist on a page but it does not change the response to the browser, there means there woulds be no feedback to tell an attacker if it succeeded. A clever technique is to inject an SQL query, which will cause the database to pause or to return a slow response if the injection works. The easiest version is to use SQL's sleep function to create a slow response. If it works the server will pause for five seconds, this tells an attacker that a vulnerability exists. Then they can execute other commands blindly, here's a more elaborate example. Which when injected will join a new query to the original one, and then use if to test if the first character of a username is char 97. Which is the letter A, if it's A then the benchmark function executes a slow process five million times. Otherwise it finishes quickly, an attacker can go through the entire alphabet in a loop, to determine the full username. This technique can be used to examine the structure of the database, table names, column names, as well as the values in the table rows. The attacker does not need to even see the text, they can just play guessing games and use fast or slow responses to know when they've guessed right. So, what are the solutions to SQL injection? First, you should limit the application's database privileges. Your application needs to read and write to the database, but it probably does not need permission to create, drop or truncate tables. It probably should not be able to grant access privileges to database users, and you should never let the application connect as the root user who can do anything including creating new user accounts. If you use the principal of least privilege it will limit the damage that an SQL injection can cause. The most important prevention is to sanitize the input. You should always escape data for SQL before using it in a query string. In simple terms SQL sanitization means rendering every single quote in the data harmless by putting a backslash in front of it. and there are many tricks to avoid detection. Most programming languages for the web, have an SQL sanitization library that you can use. Use one of those, they offer better sanitization than if you tried to write your own function. One of the best preventions is to use SQL prepared statement. In a prepared statement the SQL query is prepared with placeholders for dynamic data. It's like creating a program which expects to receive data as inputs when it's run. The data must match a specified data type such as string, or integer, and because the SQL query has already been mostly written, the data can't interfere with it, it's very effective. Here's an example, the first line sets a variable called SQL to a query string, the question mark after title indicates a placeholder for dynamic data. The second line converts it into a prepared statement, making it like a program that's ready for input. The third line adds a value as input and executes the statement, this example is using only SQL but most web programming languages include functions to make it easy to use SQL prepared statements. A third prevention is to use allow-lists and to validate data before using it in the query. This prevention is less powerful than using SQL sanitization, or SQL prepared statements, but it can be used alongside them to provide defense in depth.
- Threat models
- Least privilege
- Defense in depth
- Validating and sanitizing input
- Credential attacks
- SQL injection
- Cross-site scripting