Is this PHP code secure?

Question

Just a quick question: is the following PHP code secure? Also is there anything you think I could or should add?

    $post = $_GET['post'];

    if(is_numeric($post))
    {
        $post = mysql_real_escape_string($post);
    }
    else
    {
        die("NAUGHTY NAUGHTY");
    }

    mysql_select_db("****", $*****);

    $content = mysql_query("SELECT * FROM tbl_***** WHERE Id='" . $post . "'");

Answer 1

In this particular case, I guess the is_numeric saves you from SQL injections (although you would still be able to break the SQL statement, cf Alex' answer). However, I really think you should consider using parameterized queries (aka. prepared statements) because:

1. They will protect you even when using parameters of non-numeric types
2. You do not risk forgetting input sanitation as you add more parameters
3. Your code will be a lot easier to write and read

Here is an example (where $db is a PDO connection):

$stmt = $db->prepare('SELECT * FROM tbl_Persons WHERE Id = :id');
$stmt->execute(array(':id' => $_GET['post']));
$rows = $stmt->fetchAll();

For more information about parameterized SQL statements in PHP see:

• Best way to stop SQL Injection in PHP

Answer 2

It's a little rough, but I don't immediately see anything that will cause you any serious problems. You should note that hexidecimal notation is accepted within is_numeric() according to the documentation. You may want to use is_int() or cast it. And for clarity, I would suggest using parameterized queries:

$sql = sprintf("SELECT col1 
                FROM tbl 
                WHERE col2 = '%s'", mysql_real_escape_string($post));

In this case, $post would be passed in as the value of %s.

Answer 3

is_numeric will return true for hexadecimal numbers such as '0xFF'.

EDIT: To get around this you can do something like:

sprintf('%d', mysql_real_escape_string($post, $conn));
//If $post is not an int, it will become 0 by sprintf

Look at the snippet here on php.net for more info.

Answer 4

You have the right idea but is_numeric() may not behave as you intended.

Try this test:

<?php
$tests = Array(
        "42", 
        1337, 
        "1e4", 
        "not numeric", 
        Array(), 
        9.1
        );

foreach($tests as $element)
{
    if(is_numeric($element))
    {
        echo "'{$element}' is numeric", PHP_EOL;
    }
    else
    {
        echo "'{$element}' is NOT numeric", PHP_EOL;
    }
}
?>

The result is:

'42' is numeric
'1337' is numeric
'1e4' is numeric
'not numeric' is NOT numeric
'Array' is NOT numeric
'9.1' is numeric

1e4 may not be something your sql server understands if you're looking for what is commonly referred to as a numeric value. From an SQL injection standpoint you're fine.

Answer 5

You're not passing the connection resource to mysql_real_escape_string() (but you seemingly do so with mysql_select_db()). The connection resource amongst other things stores the connection charset setting which might affect the behavior of real_escape_string().
Either don't pass the resource anywhere or (preferably) pass it always but don't make it even worse than not passing the resource by mixing both.

"Security" in my book also encompasses whether the code is readable, "comprehensible" and does "straight-forward" things. In the example you would at least have to explain to me why you have the !numeric -> die branch at all when you treat the id as a string in a SELECT query. My counter-argument (as the example stands; could be wrong in your context) would be "Why bother? The SELECT just will not return any record for a non-numeric id" which reduces the code to

if ( isset($_GET['post']) ) {
  $query = sprintf(
    "SELECT x,y,z FROM foo WHERE id='%s'",
    mysql_real_escape_string($_GET['post'], $mysqlconn) 
  );
   ...
}

That automagically eliminates the trouble you might run into because is_numeric() doesn't behave as you expect (as explained in other answers).

edit: And there's something to be said about using die() to often/to early in production code. It's fine for test/example code but in a live system you almost always want to give control back to the surrounding code instead of just exiting (so your application can handle the error gracefully). During the development phase you might want to bail out early or put more tests in. In that case take a look at http://docs.php.net/assert.
Your example might qualify for an assertion. It won't break if the assertion is deactivated but it might give a developer more information about why it's not working as intended (by this other developer) when a non-numeric argument is passed. But you have to be careful about separating necessary tests from assertions; they are not silver bullets.
If you feel is_numeric() to be an essential test your function(?) might return false, throw an exception or something to signal the condition. But to me an early die() is the easy way out, a bit like a clueless opossum, "I have no idea what to do now. If i play dead maybe no one will notice" ;-)

Obligatory hint to prepared statements: http://docs.php.net/pdo.prepared-statements

Answer 6

I think it looks ok.

When accessing databases I always use query binding, this avoids problems if I forget to escape my strings.