-1

Given this code:

mysqli_set_charset('utf8');

$id = mysqli_real_escape_string($_GET['id']);

$result = mysqli_query($con,"SELECT * FROM post WHERE id_post = '$id'");

if (mysqli_num_rows($result) == 0) {
    header('Location: 404.php');
    die();
} else {
    // Continue...
}

And this:

$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

// New PDO...

$Ps = $Pdo->prepare('SELECT * FROM post WHERE id_post = :id');

$Ps->execute(array(':id', $id));

if ($Ps->rowCount() == 0) {
    header('Location: 404.php');
    die();
} else {
    // Continue...
}

Is there a best option to prevent SQL Injection or the are equivalent?

user2864740
  • 60,010
  • 15
  • 145
  • 220
Marcio Mazzucato
  • 8,841
  • 9
  • 64
  • 79
  • 3
    In the example the PDO one. However mysqli also supports prepared/parametrized statements. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – robbmj Nov 08 '14 at 21:26
  • 2
    The examples are apples to oranges. They can generally both be considered just as safe **if** using placeholders, which the former simply fails to do. Now, if asking about the use of `mysqli_real_escape_string` *or* placeholders (perhaps using such internally).. – user2864740 Nov 08 '14 at 21:26
  • 1
    @Philibobby, They are different questions – Marcio Mazzucato Nov 08 '14 at 21:27
  • I've never delved into this that hard, but considering both provide a means to an end, it will mostly boil down to which one you prefer using. – Jhecht Nov 08 '14 at 21:27
  • @Jhecht And the observation that I would **not hire** a developer still insisting on `mysqli_real_escape_string` instead of placeholders .. sure. – user2864740 Nov 08 '14 at 21:32
  • While I don't think this is a "good" question, and that the answers in the linked question do provide sufficient justification for the choice of approach, I do agree with the OP in that it is a [at least slight] different question.. so voting to re-open (to re-close), FWIW. – user2864740 Nov 08 '14 at 21:37
  • There's a sufficient amount of related question from which to infer a conclusion; [Why is using a mysql prepared statement more secure than using the common escape functions?](http://stackoverflow.com/q/732561), [mysqli or PDO - what are the pros and cons?](http://stackoverflow.com/q/13569). – mario Nov 08 '14 at 21:44

1 Answers1

1

Second option is the way to go. Using mysql_real_escape_string() leaves open some space for sql injection using some exotic multibyte character. Ref: SQL injection that gets around mysql_real_escape_string()

Alternatively you can use prepared statment with mysqli

Community
  • 1
  • 1
dynamic
  • 46,985
  • 55
  • 154
  • 231
  • I am just curious, is there a way to do mysql injection, if $_GET['id'] is checked, before it is placed in query? E.g. expected value is number, and code checks if $id is number/numeric value? Thanks! – sinisake Nov 08 '14 at 21:32
  • I do not know of any *current* vulnerabilities on `mysqli_real_escape_string` (which is *not* `mysql_real_escape_string`), assuming it is used with the same connection. Make sure to provide a reference for such a claim. (Also, placeholders *may* be internally implemented with escaping - but they are still a fundamentally better approach.) – user2864740 Nov 08 '14 at 21:33
  • 1
    http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string second answer – dynamic Nov 08 '14 at 21:36
  • what are you talkin about? mysql real escape string may be unsafe, read that question – dynamic Nov 08 '14 at 21:40
  • By that very answer the same issue applies to PDO, with emulation - thanks for providing the link, which is a good read. – user2864740 Nov 08 '14 at 21:42
  • Yea, that is a very obscure edge case – dynamic Nov 08 '14 at 21:45
  • @dynamic, it is mysqli_real_escape_string, but i really don't know is there any valuable difference. According to this: http://stackoverflow.com/questions/14011899/mysqli-real-escape-string-should-i-use-it, there is no real difference between two functions, in terms of security.... However, could you (or someone else) explain, if, with few lines of code, developer check if $id is number/numeric value (he can specify length, too) -> can it by bypassed somehow? Thanks! – sinisake Nov 08 '14 at 21:46