SQL injection is one of the most common website exploits. For us developers to prevent SQL injection attacks, we must first understand how it’s done. Together, let’s review the basics of SQL injection.
Remember, let’s use our knowledge on good things only, okay?
The scope of this post is only a MySQL injection sample from PHP code. I am on a Windows 7 computer, using Git Bash and running SQL commands via command line, and coding my PHP file via VIM editor. My localhost is run by XAMPP.
I will be using MySQL, not MySQLi or PDO which have prepared statements, because the SQL injection is easier to demonstrate in MySQL.
Create database
I’m running these commands via command line.
What I do is run the mysql command like this: path_to/xampp/mysql/bin/mysql -h localhost -uMyUsername -pMyPassword
Then run use MyDatabaseName; to select a database.
|
1 |
create table users (id int(11) not null auto_increment, name varchar(255), secret varchar(255), primary key (id)); |
This is a very simple database table with only 3 columns: the primary key “id”, “name”, and “secret”. The value of “secret” column, as its name implies, is user’s secret so nobody else should know about this, especially unauthorized users.
Then insert data using the following commands:
|
1 2 3 |
insert into users (name, secret) values (“Epi”, “I eat poop. Don’t tell mommy.”); insert into users (name, secret) values (“Pinky”, “I’m a spoiled brat who’s always fighting with other dogs”); insert into users (name, secret) values (“Asia”, “I eat too much my belly is touching the floor”); |
As you can see, those are dirty little secrets of our users, which in my case, are dogs in my house. :3
They are cute little chihuahuas who have embarrassing secrets just like humans. But because I’m using their secrets are samples in my database, these secrets won’t be secrets any longer. I also need to not secure my script for demo purpose. Sorry little babies 🙁 The readers don’t know you personally anyway. Hehehe
show tables; in mysql command line should show that 3 rows have been inserted.
Unsecured PHP script that runs MySQL query
Let’s save this as path_to/xampp/htdocs/test_sql_injection.php
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<?php $servername = “localhost”; $username = “root”; $password = “root”; $db_name = “test”; $conn = mysql_connect($servername, $username, $password); if(!$conn){ die(“couldn’t connect: “ . mysql_error()); } echo “Connected to db<br>”; if(empty($_GET[‘name’])){ die(“missing name param”); } $q_name = $_GET[‘name’]; echo “your input param is $q_name<br>”; $query = “SELECT * FROM users WHERE name = ‘$q_name'”; echo “Your MySQL query is: $query<br>”; mysql_select_db($db_name); $retval = mysql_query($query, $conn); if(!$retval){ die(“failed to get data:” . mysql_error()); } while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){ echo “<p style=’color:red’>”; echo “id: “ . $row[‘id’] . “, name: “ . $row[‘name’] . “, secret: “ . $row[‘secret’]; echo “</p>”; } echo “<br>That is all!”; mysql_close($conn); |
Expectation vs. Reality
Expectation: The PHP script should be accessible through this URL: http://localhost/test_sql_injection.php since we saved it in the htdocs folder. We will be able to query the ‘users’ table by supplying a GET param called “name” in the URL, like so: http://localhost/test_sql_injection.php?name=epi and the script will show us the id, name, and secret of the matched row.
It should be safe as long the attacker only searches for a name that he/she already knows, right? Hmm, nope!
Reality: If the attacker passes malicious string as GET parameter, he/she can view ALL rows in our users table.
How? I’ll walk you through it.
Exploiting the vulnerability of PHP script via SQL Injection
Note: Originally, my PHP script had $_POST data instead of $_GET and I was POSTing the ‘name’ for the query via curl --data "name=A' OR 'x'='x" http://localhost/catzie_injection.php. After changing it into GET, I couldn’t hack the script with the same string value passed to ‘name’. I don’t know why. So to make life easier, let’s just hack it by accessing the URL via web browser.
Imagine yourself as a curious person with potential in hacking, and pretend you don’t know what we inserted to the database earlier! 😆
Access the following URL with your browser:
|
1 |
http://localhost/test_sql_injection.php?name=epi |
It should should give you information about the user named Epi. Here’s what it shows:
Connected to db
your input param is epi
Your MySQL query is: SELECT * FROM users WHERE name = 'epi'
id: 1, name: Epi, secret: I eat poop. Don't tell mommy.
That is all!
Oops, we saw Epi’s secret! That’s literally a dirty little secret, isn’t it? But that’s ok, because Epi told us we can look up her secret in this database.
Now, aren’t you curious about the secret of other users in the database? Aren’t you tempted to hack into the database?
How can we hack this script so that we could view all database rows, and not just Epi’s details?
Let’s try this URL, with empty query:
|
1 |
http://localhost/test_sql_injection.php?name= |
Uhhh, nope. I only got this:
Connected to db
missing name param
Ooh, I know! Maybe I can manipulate the query string of the PHP string!!!
Let’s try this now:
|
1 |
http://localhost/test_sql_injection.php?name=x’ or ‘x’=’x |
I got this result in my browser:
Connected to db
your input param is x' or 'x'='x
Your MySQL query is: SELECT * FROM users WHERE name = 'x' or 'x'='x'
id: 1, name: Epi, secret: I eat poop. Don't tell mommy.
id: 2, name: Pinky, secret: I'm a spoiled brat who's always fighting with other dogs
id: 3, name: Asia, secret: I eat too much my belly is touching the floor
That is all!
Now we know the secrets of other users. 😆
The danger
You should understand by now that this vulnerability can lead to exposure of even more sensitive information to the attacker, such as passwords, contact numbers, addresses, etc.
Explanation of how we hacked to see all database rows
How did that happen? It’s all in the manipulated query!
The intention of the PHP script is to construct a query like this:
|
1 |
SELECT * FROM users WHERE name = ‘Epi’ |
But instead of passing a real name, we passed this value:
|
1 |
x‘ or ‘x‘=’x |
Notice that we did not add a quote at the beginning and end of the value. It’s because that value is inserted between the single quotes in my PHP script’s query string, so that it produces this query:
|
1 |
SELECT * FROM users WHERE name = ‘x’ or ‘x’=‘x’ |
The 'x'='x' in the WHERE clause will always return true, and since “or” only requires one true value, then every row in the database becomes a match for the SELECT query.
How to prevent SQL injection attacks like this?
Sorry but I won’t cover tutorials on how to do that in this post.
Now, now, don’t get mad! Basically, what you need to do is use “prepared statements” with MySQLi or PDO.
Please refer to the following articles that will give you precise instructions on how to secure your PHP scripts and web apps so that you could prevent SQL injection attacks.
