SQL Injection via URL parameter

Question

I just saw in my webstats that someone appended a lot of SQL code to one url parameter. The URLs look like this:

http://www.example.com/page.php?id=672%3f%20and%28select%201%20from%28select%20count%28*%29%2cconcat%28%28select%20%28select%20concat%280x7e%2c0x27%2cunhex%28hex%28cast%28database%28%29%20as%20char%29%29%29%2c0x27%2c0x7e%29%29%20from%20%60information_schema%60.tables%20limit%200%2c1%29%2cfloor%28rand%280%29*2%29%29x%20from%20%60information_schema%60.tables%20group%20by%20x%29a%29%20and%201%3d1

http://www.example.com/page.php?id=convert%28int%2cdb_name%28%29%29--

http://www.example.com/page.php?id=999999.9%20union%20all%20select%200x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536%2c0x31303235343830303536--

and some more...

My code looks like this:

$myid = intval($_GET['id']);
$stmt = $con->prepare("SELECT *
FROM mytable AS r
WHERE r.ID =:ID");
$stmt->bindValue(':ID', $myid, PDO::PARAM_INT);

My questions are: Is my code secure? And how can I check what the result of these queries was? I mean my page only echos the variables I asked for. But the attacker of course wants to see the things he/she queried for.

Answer 1

It is secure. In a prepared statement, the parameter value is never actually interpolated into the query string. The query is sent to the database server before the parameters. Thus, no chance of an injection. In your example:

Sending to the database server:

$stmt = $con->prepare("SELECT * FROM mytable AS r WHERE r.ID =:ID");

Sending the parameter(s) to the database server:

$stmt->bindValue(':ID', $myid, PDO::PARAM_INT);

This is unless you're using emulated prepared statements. To enable prepared statements:

$con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Answer 2

You are lifting this log from the wrong end.

It doesn't matter, where the data has come from, be it URL, or JSON object, or a file of whatever.

But it's only destination that matters. So, everything that goes into query via prepared statement is perfectly safe. Just because it's the very purpose of prepared statements.

So, most of your precautions are too redundant and whole code can be just 2 lines

$stmt = $con->prepare("SELECT * FROM mytable WHERE ID = ?");
$row  = $stmt->execute([$_GET['id']])->fetch();

Answer 3

You actually have two lines of defense, which is very good.

The first one is intval(), which will return 0 for non-numerical input.

The second is the actual prepared statement. The one which you're using is good, just keep it that way.

However, just using prepare() does not automatically make your code immune to SQL injection. what you have to do is to prepare a constant SQL string with placeholders. For example, consider code like this:

$stmt = $con->prepare("SELECT * FROM mytable WHERE $cond");  // NEVER DO THIS!

where $cond is a string that may contain arbitrary data, including dangerous one.

(Sometimes, it is useful to be able to dynamically construct part of an SQL query, which is why writing something like the example above can be so tempting. But even in this case you have to create a constant SQL out of hardcoded query parts in your script, adding placeholders for the arbitrary data literals, but it's not a trivial task. Some database abstraction frameworks may provide helper methods to let you do this more easily and safely.)

What actually does make your code immune to SQL injection is to never insert arbitrary data literals into SQL code, but via placeholder only. The very purpose of prepared statements is to allow you to run a query this way, by letting you use placeholders and bind values to them instead. That's what makes them so useful from a security viewpoint.

So, to recap: as long as no arbitrary data literal ever makes it into your SQL statements, you should be safe from SQL injection. In your example, your SQL statement is a constant string (with a PDO placeholder for the arbitrary data), so you're OK.