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.

Solution 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);

Solution 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();