Security Basics: SQL Injection
The vulnerability was first documented in 1998 by Phrack contributer and security researcher Jeff Forristal. The internet never recovered.
As one of the most historically prominent vulnerabilities out there, SQL injections have likely caused millions (if not billions) of dollars worth of damages to thousands of companies over the last 20 years. The vulnerability was first documented in 1998 by Phrack contributer and security researcher Jeff Forristal. The internet never recovered.
So what is it?
To understand what a SQL Injection Attack is, we need to have a brief primer on how User Interfaces typically communicate with the backend or database:
When a user submits a form, or takes some other action requiring feedback from the backend, that data is communicated either with the help of an API or with a language like PHP that communicates on the server level but is triggered in-browser.
In the case of an API, the browser sends a request to an endpoint that contains all of the data needed to fulfill a request. The API then sends a response, and the code in the webpage handles the received data. With PHP, the code that speaks to the server is loaded by the webserver itself and is triggered by calling the file name from the browser.
For example, to submit a form, a file named ‘submit.php’ could be declared as the ‘action’ in the HTML of the form. When the user clicks the button to submit, that file is then called and parameters are sent either through POST data, or as URL parameters in a GET request.
On the server, let’s say the data is being used to query a database. The unsafe way to do this, that opens the application up to an injection attack, is to simply interpolate the supplied data into the database query (most commonly a SQL query).
A Simple Example
Here’s an example of some server side code that might handle the request:
You can see that the $size variable, which is coming from the request, is essentially being placed into the query without transforming the data contained whatsoever. This means that an attacker can place whatever data they would like into the query. Notice that the $size variable is enclosed in single quotes in the query.
The query is expecting a string enclosed in these quotes, and will accept anything provided so long as it is the correct syntax. You might also notice that the SQL query isn’t explicitly closed with a semicolon, which is also bad practice.
Let’s exploit it!
With this code, there’s nothing stopping an attacker from sending something this with the request:
size=’ OR 1=1; —
which would transform the query into this:
SELECT id, name, inserted, size FROM products WHERE size =’’ OR 1=1; —
This transforms the intended query into something unintended; the query now returns every record because the OR statement says ‘if the size is this, or 1=1’. Well, 1 does equal 1, so all records match that query. The double hyphens comment out anything following, essentially blocking it from being part of the query.
Something more malicious might take the form of this request:
size=’; DROP TABLE products;
Which would execute a query to drop the products table after executing the first query.
The possibilities are endless. An attacker can query tables like information_schema.tables to see which tables are in a database, and then begin to enumerate those tables without having any prior knowledge of how the database is setup. This is how user data is stolen and data is manipulated.
So how can you stop something like this from happening? Sanitize user input, and don’t plug data directly into a SQL query. Sanitization just means either removing or ‘escaping’ characters that can mess with a query (like quotes, hyphens, and semicolons).
You can avoid plugging data directly into a query by using an ORM library, or Object-Relation Mapping, in your code which provides a layer of abstraction from the raw queries and will often come with built-in protections from injection attacks and other vulnerabilities.
That’s basically it. In the wild, these injection points are often hard to reach and take lots of trial and error and special tooling to uncover due to the age and exposure of the vulnerability. But, they’re still out there, ready to be exploited by those with the time and knowledge.
Ethical hacker and IoT security specialist.