SQL Injection in Plain English

This is the first post in a weekly series where I will attempt to explain different types of website attacks, avoiding the heavy security jargon commonly found in articles of this nature. You’ll never see me use phrases like ‘threat modeling’, ‘attack vectors’, or ‘risk factors’. Today, I’d like to describe one of the most dangerous types of website attacks: SQL injection. Sadly, it’s one of the most common types of vulnerabilities, despite being one of the easiest types to prevent.

Most modern websites are powered by a web server that communicates with a database. That database is used to store anything that’s provided or generated by the website’s users, including private information like login credentials and credit card numbers. The web server accesses this information using SQL (Structured Query Language). An SQL statement essentially asks the database to retrieve or store some information, using plain English sentences. These natural language-like statements are sent from the web server to the database as raw strings that the database driver then has to parse and turn into actionable commands:

String sql = "SELECT id FROM users
                        WHERE username = 'angel'
                          AND password = 'hunter2'
                        LIMIT 1";
Statement stmt = connection.createStatement();

The Danger

Now, code like this would be useless if it were not dynamic. If I hosted a banking website that used a similar SQL statement during the login process, I would want to take what users type into my login form and generate the proper SQL statement accordingly. A naïve implementation that comes to mind is something like this:

String sql = "SELECT id FROM users" +
  " WHERE username='" + request.getParameter("name") + "'" +
  "   AND password='" + request.getParameter("password") + "'" +
  " LIMIT 1";

This might seem like a benign operation, taking a few strings and concatenating them, until we remind ourselves that the resulting string is actually code for the database to execute, and that we’ve just given the user full reign over a piece of that code. A malicious user could type this into the password field:

' OR id=1 --

This seems bizarre at first glance, but when viewed in the context of the full SQL string, this forces the SQL statement to return the user ID 1, meaning the malicious user has just found a way to log in as a totally different user.

Here’s the completed statement with the injected password value highlighted:

SELECT id FROM users WHERE username='hacker' AND password='' OR id=1 --' LIMIT 1

On the other hand, here is the completed statement with syntax highlighting that illustrates how the database would actually parse it. Notice how -- is used to comment out the unmatched single quote. This allows the statement to remain valid, since -- is used to signify the start of a comment in SQL:

SELECT id FROM users WHERE username='hacker' AND password='' OR id=1 --' LIMIT 1

This malicious user could cause much further damage, like forcing an account balance transfer:

' OR (UPDATE accounts SET balance=999999 WHERE id=47; COMMIT;) --

shutting down the database:


or even deleting files accessible by the database’s operating system:

' OR SELECT OS_COMMAND.EXEC('rm -f /important/file') FROM dual; --

The Answer

As web developers, we thankfully have a tool that easily and completely prevents such attacks: the parameterized statement. A parameterized statement is a SQL statement with placeholders, such as ?, that are substituted with real values during execution. Normally, parameterized statements are used as a performance optimization technique: a database can run a parameterized statement many times with different variables without the need for the database driver to re-parse the SQL string each time. Parameterized statements also happen to have the side-effect of preventing SQL injection: the variables are sent to the database separately from the statement string and thus are never treated as commands. Our login example should have actually been written like this:

String sql = "SELECT id FROM users WHERE username = ? AND password = ? LIMIT 1";
java.sql.PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, request.getParameter("name"));
stmt.setString(2, request.getParameter("password"));

Written as above, the risk of SQL injection has been eliminated, regardless of what special characters or command strings are in the password request parameter. The solution is brilliant in its simplicity.

Now it’s up to you go through your code and make sure all of your SQL statements that involve user-generated content are properly parameterized. I’ll admit, it can be difficult to spot all SQL injection possibilities in large or rapidly changing codebases, but for that, Tinfoil Security is here to help. Tinfoil provides the best web application security solution on the market, and it detects SQL injection vulnerabilities on your website along with many other types of web vulnerabilities.

Angel Irizarry

Angel Irizarry is the Software Samurai of Tinfoil Security, and a self-proclaimed software purist. All he needs to do his best work is a plain Linux machine with Git and Emacs installed. He loves everything about front-end development, like making pages interactive and super fast, even if that means digging in and optimizing some SQL. When he's not writing code, which isn't very often, you'll find him on his iPad scouring his RSS feeds for news and rumors of cool new gadgets.

Tags: sql injection plain english