Back in my exploiting days, SQL injection was pretty much popular. I guess it still is at the moment, but because I’m out of the scene, I guess I can’t assure it. Well anyway, I’m gonna show how SQL injection is done, with a sample script and database table. I won’t go into detailed information about SQL injection, this is the actual execution.
First create a test database, and a test table with basic user details.
CREATE DATABASE `sqltest`;
CREATE TABLE `sqltest`.`users` (
`id` INT NOT NULL ,
`username` VARCHAR( 20 ) NOT NULL ,
`password` VARCHAR( 32 ) NOT NULL ,
`address` VARCHAR( 50 ) NOT NULL ,
`email` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `id` )
)
Then we fill it with random information. As for the password, lets be advanced and put a md5 hash for it.
INSERT INTO `sqltest`.`users` VALUES ('', 'ruel', '34819d7beeabb9260a5c854bc85b3e44', '9B Kingston Street, Westchester, CA 90045', 'myrandomemail@domain.com');
Now, we create a simple PHP script called viewuser to output the data we stored to the database.
<?php
// Make sure there's a GET request first
if (!isset($_GET['id'])) {
die;
}
$id = $_GET['id'];
// Your server details goes here.
$conn = mysql_connect('localhost', 'username', 'password') or die(mysql_error());
mysql_select_db('sqltest', $conn) or die(mysql_error());
// This is our query
$sql = "SELECT username
FROM users
WHERE id = " . $id;
// Execute our query
$result = mysql_query($sql) or die(mysql_error());
// Check if there are results
if (mysql_num_rows($result) == 0) {
die;
}
// Get the username
while($row = mysql_fetch_assoc($result)) {
$username = $row['username'];
}
?>
<!doctype html>
<html>
<head>
<title>View User</title>
<style>
body {
font-family: Arial;
font-size: 1.5em;
color: #555;
}
#userblock {
margin-top: 200px;
text-align: center;
}
</style>
</head>
<body>
<div id="userblock">
Hello! I'm <strong><?php echo $username; ?></strong>
</div>
</body>
</html>
<?php
mysql_close($conn);
?>
Next step is to try it: viewuser.php?id=0. And it works!
It works!
Then lets start injecting SQL codes. First of all, since we already know the table structure, we can easily extract information from the database using SQL injection. Now, we will pretend that we do not know such information, and lets start by finding out how many columns shall we pass to UNION ALL SELECT. This is done by injecting the ORDER BY statement in the url, plus an integer. If the page returned an error, then we get our number of selected columns.
Let’s start by 1 : viewuser.php?id=0 ORDER BY 1 – No error. Then we try 2 : viewuser.php?id=0 ORDER BY 2 – Error: Unknown column '2' in 'order clause'.
What does that mean? Column number two doesn’t exist. That means, there’s only one selected column. And yes, if you would look at our code, only the column username is selected.
Now we inject UNION ALL SELECT. But first, we do not know what fields are present in the database. As far as I know, in blind SQL injection, there’s no way of retrieving column names with a query. The only way would be bruteforcing. If the page showed the data you were referring to, then the column name is correct, otherwise, it will return an error.
The same goes for the table name, it needs bruteforce. You have to try and try until you get the right table name and column name.
Since we only have one selected column, we can only pass one argument to UNION ALL SELECT. The query will be injected as: viewuser.php?id=0 UNION ALL SELECT (column) FROM table
Now, its your turn, try the fields in our structure and examine the output. Here’s what it looks like when passing the password field as an argument: viewuser.php?id=0 UNION ALL SELECT (username) FROM users
Hashed password
Yes, that’s the password we inserted in the database earlier. And please take not that in the actual script, we didn’t mean to show any other fields except username.
Some of the password fields in poorly coded websites are in plain text, so this is fairly easy for attackers to get in the system.
How do we secure the script? Well its pretty easy. We’ll make use of mysql_real_escape_string() function in PHP. And this is how it’s done:
$sql = "SELECT username
FROM users
WHERE id = '" . mysql_real_escape_string($id) . "'";
And if we will inject our last query: viewuser.php?id=0 UNION ALL SELECT (username) FROM users, it won’t work anymore.
SQL injection is pretty powerful. Actually you can DROP the whole table if you want to. The possibilities are quite, endless. And poor coding causes this. There are also more ways to resolve this issue: input validation, mysqli, etc. Practice secure coding, especially on public scripts.
