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 system) software 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.