A while back, I made a post talking about a Linkedin password dump that let hackers gain access to Mark Zuckerberg’s twitter account. We don’t know how it is that hackers got their hands on the millions of Linkedin passwords, but it got me thinking of a certain type of attack that is much more common than it should be, and one that you should take care of avoiding if you run a web application. This attack is commonly known as an SQL injection (often pronounced “sequel injection”), and it uses something as harmless as your search bar to access the web application’s database. 

SQL stands for Structured Query Language and is essentially how most programs talk to databases. It was developed in the prehistoric era of web-computing of 1974 and is actually very good at its job despite its age. It’s a very simple language to use and works very well with different other languages, such as PHP. PHP is an incredibly powerful and intuitive programming language, but unfortunately not the safest. It is one of the most widely used programming languages for web application, with Facebook, Yahoo, and Wikipedia all being at one point coded in it, however, it does not come with any built-in security, and hackers can easily access web applications that are badly built to get information you definitely would not want them to have.

Here’s how it works. A typical SQL query looks something like this:
SELECT ? FROM ? WHERE ? LIKE ‘QUERY‘;
Let me explain what it is I just typed out. Let’s pretend that I own a badly made online store for craft supplies. If I wanted to see the store’s selection of popsicle sticks, I would use its search bar to search for popsicle sticks, and it would give me a result of the name of the item (popsicle stick), the price of the item (12.99$), and the quantity of the item (500). I type in the search query that PHP sends to the site’s database as an SQL query where it selects and item (I don’t know what, hence the question mark) from a table, (again, don’t know what table this will be) where the column (another mystery) is like my query. Hence, I get a result. I can also type in a term like “opsicle stic” to get the result I want which means that there are wildcards on the side making the SQL query look something like this:
SELECT ? FROM ? WHERE ? LIKE ‘%POPSICLE STICK%‘;
As a hacker who doesn’t have access to the back-end code, I can only manipulate what’s in the quotation marks. So, what would happen if I entered just a quotation mark as my search query? If the website isn’t coded against it, the SQL query will look something like this:
SELECT ? FROM ? WHERE ? LIKE ‘%%’;
The program assumes the quotation we entered as the query is the ending quotation of the query, and doesn’t know what to do with the last three symbols. This will return an error, even though a product in a craft supplies shop can have a quotation mark in its name, for example, if it was named something like Burt’s Pipe Cleaners. Here’s how a hacker can abuse it. If you add a semicolon, and then a comment sign (two dashes in MySQL, for example) to indicate a comment in the query, it becomes:
SELECT ? FROM ? WHERE ? LIKE ‘%‘;— %’;
Which will ask the database for simply a wildcard, meaning everything. A hacker now can input commands into PHP by using this trick (which, again, can be prevented relatively easily) to access sensitive info. Here’s an example, let’s say the hacker wanted to have a timer on the response a website gives out. If they were trying to access a site that they know uses MySQL, for example, they can make a query of
popsicle stick’ AND 1 = SLEEP(2);– 
to make an SQL query of

SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘AND 1 = SLEEP(2);— %’;
which will make the server wait two seconds for every query found.

Hopefully, this helped you understand just how easy an SQL injection really can be. Just remember that hackers don’t usually go after big targets, it’s much easier to catch a smaller fish that doesn’t care about it’s web app security as much, which could be you.

I recently wrote a post explaining in brief about the dangers of SQL injections. I ended the post explaining how it was possible to run commands through a search bar in order to access and control a web application’s database. I decided that it would be better if I went a little more in-depth and wrote out what a typical SQL injection attack can look like against an unprotected database.

So, as I wrote in the previous post, let’s pretend that I am a proud owner of a craft supplies store, for arts-and-crafts and fun little projects to do with the kids. My craft store uses PHP and MySQL to access its database of items. In my previous post, I explained the basic way SQL injections are run. Essentially, adding a comment in the right place will allow a hacker to secretly hide commands for PHP to carry out. If you aren’t sure how it works, the basics are all there.

I gave an example of a command to make the database wait a certain amount of seconds before giving a response, which sounds pretty useless. Why waste time making your own hacking process slower?  The reason behind it is there is a valid form of SQL injection called a “blind injection”, where one would use SQL injection commands without expecting to get any visible output. What I mean is, for example, to test what kind of RDBMS (relational database management systemsoftware the web app uses. MySQL has a sleep function called SLEEP(), another software may use something like waitForDelay(). By testing out the different functions between different software, a hacker can get a better understanding of what they’re dealing with. Another thing someone could do would be to ask to receive data from a table that they aren’t sure exists, along with a delay. If there is a delay but no output, the hacker will know that the table exists, but the output is hidden somehow.

Let’s say a hacker has happened on my website and wants to steal the passwords that I have stored on it. They can show those passwords by what is called a union. A union is the sandwiching of two tables one under the other, provided they have the same number of columns. The hacker assumes when they type in to search for “popsicle sticks” that the table outputted has three columns, one for the name, price, and quantity of the item. They can test this hypothesis by entering the SQL query
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT 1,2,3 FROM dual);— %’;
where the UNION function should output the numbers 1, 2, and 3 as separate columns from the “dual” table. The “dual” table is a blank hypothetical table used for testing in MySQL, which the hacker knows is the software used thanks to the previous injection.

If the result is the numbers 1, 2, and 3 printed out successfully at the bottom of the table, the hacker’s in business. They now know that they can output data to the table, which is world-endingly bad for the creator of the website (me). MySQL has an information schema called information_schema.tables that contains information about the different tables in the program. The hacker can access this by making the SQL query
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT TABLE_NAME, TABLE_SCHEMA, 3 FROM information_schema.tables);— %’;
which will output the table name, what kind of table it is, and the number 3, because the number of columns has to be the same always.

The hacker will scroll through the many different table names and find a table that I created called “users”, which is where I could keep all of my different user data, maybe, we’ll see (yes, of course, don’t you worry). The hacker doesn’t want to simply union this new table onto the current one because there may be a different number of columns than 3. They would instead write an SQL query of something like this
SELECT ? FROM ? WHERE ? LIKE ‘%popsicle stick%‘ UNION (SELECT COLUMN_NAME, 2, 3 FROM information_schema.columns WHERE TABLE_NAME = ‘users’);— %’;
which will output the different column names from that table.

From the columns, the hacker will find a column named “userPasswords”, which they can output with an SQL query, and then dump onto the internet.

This was just one way in which a hacker can use SQL injections to gain access to personal information on unprotected sites. It’s quite a bit like cracking a puzzle, and just like doing puzzles, along with most other intellectual things, a robot can most likely do it better. This is why, as a side note, always remember to have some sort of spam protection, so that your website isn’t bombarded with thousands of SQL hack attempts.