1

So I understand PDO Prepared Statements should protect from SQL injection and ' escapes. But when I attempted the following...

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["id"]))
{   
    $id = $_POST["id"]; 
    //$id = "2' AND name='Entry2";
    $someinfo = "updated";

    ...DB Stuff...

    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $dbpassword);

    $stmt = $conn->prepare("UPDATE testdb SET info=:someinfo WHERE id=:id");

    $stmt->bindParam(':id', $id);
    $stmt->bindParam(':someinfo', $someinfo);
    $stmt->execute();

    $conn = null;

    exit();
}

Then the row with id=2 and name=entry2 would be updated. Now it doesn't seem like this can be used to escape into other SQL queries, and I assume I can take precautions to ensure this kind of escape can't really do damage. But I wanted to be sure that there wasn't some other way to prevent ' escapes making unexpected changes to SQL query parameters. (Worth noting, I tried something similar in SQLi and got pretty much the same result.)

Is there something I'm missing? Or is this just the way Prepared Statements work.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
James P
  • 119
  • 1
  • 10
  • 1
    [Extremely related.](https://security.stackexchange.com/questions/15214/are-prepared-statements-100-safe-against-sql-injection) – Script47 Sep 26 '17 at 11:34
  • 3
    Please take the time to go over the documentation http://php.net/manual/en/pdo.prepared-statements.php if you haven't already done so. – Funk Forty Niner Sep 26 '17 at 11:37
  • 1
    Ok. There seems to be a misunderstanding. I am not wondering if this might happen. This is happening, exactly like I described. Maybe I should make a video? – James P Sep 26 '17 at 12:11
  • To be clear this doesn't happen inside the "SET ... WHERE" area. I tried it worked as expected. But it certainly is happening in the "WHERE ..." part of the query. Has anyone telling me this won't happen tried this? – James P Sep 26 '17 at 12:13
  • @chris85 Yes, this is my actual code. I've tried it dozens of times in various ways and poured over the suggested answers long before posting this. I checked again right after reading these responses to make sure I wasn't crazy. If someone tried it and gets a different outcome then I would be interested to hear it. Because I got pretty much the same thing in mysqli, I'd guess whatever is happening is happening under the PDO layer (PDO wraps mysqli, right?) In case it's relevant, this is PHP 7.0. – James P Sep 26 '17 at 12:40
  • @chris85 Response above. – James P Sep 26 '17 at 12:41
  • @chris85 I commented out the first bindParam ($stmt->bindParam(':id', $id);) and got "Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in ..." And of course nothing was updated. Hopefully I understood your suggestion. If not let me know. – James P Sep 26 '17 at 13:13
  • @chris85 Yeah, I go back and reset it every time to be sure. It predictable changed it to the given string. https://imgur.com/a/2Ufil Shows the databse after the update in case it helps. (I created a new project just to isolate this.) Thanks for your help btw. – James P Sep 26 '17 at 13:33
  • @chris85 It returns: string(19) "2' AND name='Entry2" I should note, there isn't any other code besides what I posted. I can post the HTML if you want, but it's bare bones, just a click and execute post method, and a way to return anything the PHP spits out. I stripped down everything to make sure I isolated whatever this is. – James P Sep 26 '17 at 13:42
  • @chris85 Yes, that is pretty much exactly my code. Please share your results. Thanks again for your help. – James P Sep 26 '17 at 13:55
  • 2
    @chris85 He was right, it's answered here https://phpdelusions.net/pdo#comment-277 – user3796133 Mar 08 '18 at 03:37
  • @JamesP Aha, I was mixing two things together, you are correct. I've removed previous comments to avoid future confusion for visitors. – chris85 Mar 10 '18 at 15:13

1 Answers1

4

After looking around some more, this behavior was eloquently explained/solved for me here: https://phpdelusions.net/pdo#comment-277

It turns out it's not escaping the string, but instead truncating input after the integer which just made it appear to escape the string. I was able to confirm this upon modifying the code.

James P
  • 119
  • 1
  • 10