Dangerous Injections
How hackers can peer into insecure web connected databases
A Persistent Problem
SQL injection attacks are not new. They have been around since when websites first became connected to databases. Misbehaving hackers figured out that they could sometimes manipulate a website’s URL and access data that was not intended for them. Or, if they worked at it, they could find clever ways to destroy data in a database that is connected to to a website simply by passing a bit of SQL in the query string. Imagine something like this SQL statement being executed, initiated by an outsider on the attack, simply by tinkering with a website’s dynamic url.
select * from users; drop table users;Those two statements, run sequentially, would be catastrophic. Poof! The users table is vaporized.
If that’s not frightening enough, according to w3schools.com, SQL injection is …
… a code injection technique that might destroy your database.
… one of the most common web hacking techniques.
… the placement of malicious code in SQL statements, via web page input.
I would also add that SQL injection is possible with tools like Postman, which mimic http requests and responses. Postman and other tools like it are invaluable for testing API end points, which we will do here.
In this post, we will be using Postman to make our own simple SQL injection attack on our own an unprotected node / express based web server with a sqlite3 database using the node-sqlite3 npm package. Express is a fast, un-opinionated, minimalist web framework for Node.js. We’ll make this simple attack to better understand how SQL injection works.
The express web server below is created within a single JavaScript file called server.js and another called utils.js, which contains the functions where database queries are made. Here you can see various routes defined. The following server.js web server code queries the database based on an http request route. The player data from the database is returned accordingly.
A demo of the web server above can be visited here. Think of this as a simple API endpoint, one that is not very secure.
The lines to focus on are 21 and 29. On these lines, functions are called from utils.js. These functions return data from sqlite3 database queries. We pass a database object to the function, and, in the case of getPlayerByEmail, we also pass the email address of the player we are querying for. This email address comes DIRECTLY from the email value being POSTed to the /playerByEmail route.
In utils.js we can see the underlying SQL statements in each of the functions. The getPlayers function takes no input from the outside, but the getPlayersByEmail does use the email string passed in directly in the template literal creation of the const sql on line 16 below. It assumes that the email string is valid and harmless.
So, let’s visit our routes in a web browser.
If we visit our web server at the / route, we get a welcome message as expected. No database queries here.
http://138.68.23.63:4060/
Next, we visit the /players route and we get the array of the players from the sqlite3 database query. Looks good.
http://138.68.23.63:4060/players
Lastly, we have the /playerByEmail route. It can only be reached by HTTP POST because of its signature in server.js. We can use Postman to mimic a POST request, sending an “email” value that will be used by the API to assemble the string SQL query with a where clause.
POST: http://138.68.23.63:4060/playerByEmail
Here we send a POST request with the “email” value being passed, and we get the correct matching user.
If we send the other valid emails, they return the correct users as well.
Everything seems okay…
…except, the API is open to a SQL injection attack. Why is this?
Using Postman, it’s easy to start prying and find out. Let’s say we don’t just harmlessly pass an email address string, but we pass a string starting with an email, then we tack on some extra SQL after the address that is a SQL union (see bold text below). Here is the data we send in our request this time…
{
"email": "jump23@bulls.com' union SELECT 1,sql,3 FROM sqlite_master WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' AND name = 'players';"
}The format of the SQL injection portion comes from this document, Injecting a SQLite database based application, which illustrates many nefarious SQL injection strategies.
By injecting the SQL union after the email address, a hacker can maliciously query information about the nature of all the columns in the players table or any other table where name is known (or guessed). See below where the response reveals the CREATE TABLE statement for the players table and all of its column names and types! Ouch!
Here we can see the attack from perspective of the back end with a console.log of the offending SQL when it occurs. Notice how the union SQL string is appended to the intended SQL executed on the back end.
dangerous injection:
SELECT name, city, email FROM players WHERE email='jump23@bulls.com' union SELECT 1,sql,3 FROM sqlite_master WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' AND name = 'players';'This injection works because the back end code simply assembles the SQL statement from the email string passed in. No checking is made on the email string.
While this example does not destroy or change data, once the hacker finds an opening like this and obtains column names of a table with sensitive information (imagine social security numbers or other sensitive personal data), it is only a matter of time before damage can be done such as deletion of data or theft of sensitive data.
How can we stop SQL Injection?
Luckily, preventing SQL injection is not difficult, it just takes a bit of care to make sure that user input is never assembled into a SQL string directly without mitigation. Most platforms provide ways to use parameterized SQL, which addresses SQL injection vulnerability.
Here we have the two functions that are intended to do the same thing (return the user data for the email passed in). The first function (getPlayerByEmail) is open to SQL injection because it does not use a parameterized query, while the second function, getPlayerByEmailStrong uses a parameterized query and is immune to a SQL injection attack. In getPlayerByEmailStrong, the db.all function from the sqlite3 library takes in an array of parameters [email], which are mapped to the ? (question marks) in the SQL string when executed. This is where the offending SQL is blocked.
Here we can see in Postman that if we post to getPlayerEmailStrong with the malicious SQL tacked on, it does not return player table data as the getPlayerByEmail did above. It just returns an empty array. This is because the SQL injection vulnerability has been closed by parameterizing the email address.
Further Protection
While Object Relational Mappers (ORMs) do not prevent SQL injection vulnerabilities, they do provide an extra layer of code and abstraction between user input and the database. Also, they provide other advantages and are worth considering for the back end of an application.
Notably, most ORMs such as Active Record and Sequelize allow for raw sql queries to be executed within the framework of the ORM. Such SQL statements would be vulnerable to the SQL injection described above if not parameterized.
Demo
The github repo and source this post can be found here: https://github.com/FergusDevelopmentLLC/sql_injection_api
References
Express.js, a fast, unopinionated, minimalist web framework for Node.js
node-sqlite3, a sqlite3 database connector for Node.js
Injecting SQLite database based application, SQL injection techniques
Hacker Icon made by Nikita Golubev from www.flaticon.com
Syringe and server icons made by Freepik from www.flaticon.com
Who is/was the best of all time? Please comment below. :)
