In last time i saw more often security holes in web applications from web developers that actually do create web applications and run them – SQL injections.
I do not understand that, because avoiding them is actually no voodoo.
1. Understanding SQL injections
Its easy. You instruct a mysql server to do things with SQL commands. At the application level these are normal strings consisting of a human readable syntax.
So this is actually where exactly the injection can happen.
Usually you will not write harmful SQL code on your own, so where do they really come from?
This query does not look harmful:
$sql = ‘SELECT `id`, `name` FROM `accounts`’;
This query does:
$sql = ‘SELECT `id`, `name` FROM `accounts` WHERE `id` = ‘ . $id;
Just depending where your $id does come from.
Consider the following code:
$aid = $_GET[‘aid’]; // read account id from GET parameter
$sql = ‘SELECT `id`, `name` FROM `accounts` WHERE `id` = ‘ . $aid;
mysql_query($sql);
…
This can lead to a SQL injection, as I could do the following
curl “http://yourwebapplicationhost/account.php?id=0%3B%20DROP%20TABLE%20%60accounts%60%3B”
The above code just means I can directly manipulate the SQL command.
I just need to end the SQL “SELECT” and add a SQL “DROP TABLE `accounts`;”
Its the same like:
$aid = ‘0; DROP TABLE `accounts`’;
$sql = ‘SELECT `id`, `name` FROM `accounts` WHERE `id` = ‘ . $aid;
mysql_query($sql);
…
I could give more examples. But the idea should be clear now.
No. Magic quotes is not a fix. Its rather a mess and deprecated. Just qoogle that.
2. How to prevent SQL injections
2.1 Escaping
A key to success could be escaping. In easy words escaping means that “user input” data will be prepared to be safely used in a SQL query.
If tied to old functional mysql API you just need to do it with mysql_real_escape_string(). If using PDO just have a look at: PDO::quote()
So our code could be fixed like:
$aid = $_GET[‘aid’]; // read account id from GET parameter
$sql = ‘SELECT `id`, `name` FROM `accounts` WHERE `id` = ‘ . mysql_real_escape_string($aid);
mysql_query($sql);
…
2.2 Prepared Statements
A better way to prevent SQL injections is to use PDO with prepared statements.
Prepared statements have advantages and disadvantes too.
2.2.1 Advantages:
Prepared statements are SQL commands without the actual parameters. They are more like templates for queries of the same type. They become compiled in the SQL server and can be reused which gives a performance gain as compiling is only required one time.
I tested it once and if i remember right, my application speed increased by 20%
Unfortunatly web application requests have a very short life time so that reusing them does not often makes that much sense.
Well, as the parameters are not included in the SQL command you just can not tweak the SQL command but only its parameters.
2.2.2 Disadvantages:
You need one more request to the database server to set up the prepared statement.
You cannot use parameter binding in SQL commands like:
SELECT `id`,`name` FROM `accounts` WHERE `id` IN(:id1, :id2)
You would have to use PDO::quote() again and construct your SQL like:
// set up ids
$ids = array(1,2);
// quote ids
foreach($ids as &$id) $id = PDO::quote($id);
// construct SQL
$sql = ‘SELECT `id`,`name` FROM `accounts` WHERE `id` IN(‘ . implode(‘,’, $ids) . ‘)’;
…
3. Conclusion:
You should always be alerted if putting input data not constructed directly from or not validated of your application itself from like $_GET, $_POST, $_REQUEST, … into e.g. a SQL query and thus escape it.
Its even better to have a abstract security concept which might be e.g. using prepared statements.
…
4. XSS/HTML injections
Injections mean injecting something! So injections are all similar.
A simple example of a XSS or HTML injection would be:
$userName = $_GET[‘username’];
echo ‘Hallo ‘ . $userName;
So with this kind of script i can easily put HTML or Javascript on your page just by putting some HTML or Javascript into a GET parameter “username” when calling the script.
You might want to have a look at strip_tags() or using certificates.
I think, i dont need to explain that further.
5. Code injections
Its the same with code injections. The difference is just where you put the injected data. With PHP code injections a candidate e.g. is:
$code = $_GET[‘code’];
eval($_GET[‘code’]);
6. So what?
So this article may not be complete but it should at least make up an idea about this topic in your mind.
Please be aware that this article is about injections related to PHP, but they work the same way in other languages as well like javascript, java, …