Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts

    Question about PDO and ? placeholder

    I was curious, for a situation where you use the ? placeholder such as:

    Code:
    $mysql->prepare('SELECT * FROM table WHERE column = ?');
    $mysql->execute(array($_POST['value']));
    Does PDO sanitize the value there like it does with bindValue/bindParam? Or should I be sanitizing before it gets there?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Dormlich could likely confirm. Given that execute with an array treats everything as PARAM_STR, I would expect that it implicitly escapes the string identically to providing a string in a bind. Unfortunately, the API is not clear on this, although does suggest that you must EITHER pass the array to execute OR bind, so I would infer from that it is identical to bindParam.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by Fou-Lu View Post
    Dormlich could likely confirm. Given that execute with an array treats everything as PARAM_STR, I would expect that it implicitly escapes the string identically to providing a string in a bind. Unfortunately, the API is not clear on this, although does suggest that you must EITHER pass the array to execute OR bind, so I would infer from that it is identical to bindParam.
    Thanks. It was that vagueness that brought me here. I realize how I could have tested with an injection, but given the only sql server's I have to work with atm are clients', I'd rather not risk anything.

    Actually Fou-lu, if you have experience with PDO, could you address this other question I had? http://www.codingforums.com/showthread.php?t=249481

    I wasn't sure on bumping rules, so decided to leave it.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by Keleth View Post
    Thanks. It was that vagueness that brought me here. I realize how I could have tested with an injection, but given the only sql server's I have to work with atm are clients', I'd rather not risk anything.

    Actually Fou-lu, if you have experience with PDO, could you address this other question I had? http://www.codingforums.com/showthread.php?t=249481

    I wasn't sure on bumping rules, so decided to leave it.
    I don't have a lot of PDO experience since I typically write using the native MySQL[i] or SQLServer drivers, and PDO came after when I'd need to use it. So I dump everything through my own abstraction layer and extend to make use of the native libraries instead of using PDO. When I converted from procedural to OO, I probably should have lay weight to the PDO, but that was still early 5.0 version and I typically avoid both pecl and pear extensions when I don't need them. PDO was not native in PHP until 5.1.

    dormlich appears to exclusively use PDO, so I'm sure he can help you with any questions regarding PDO.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Okie, will do, thanks. I'll wait a bit to see if he notices this thread, and if not, I'll go ahead and send him a PM.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Regardless of the "protection" built into such calls you should always validate the data if it was input by a person or sanitize it if it was obtained from elsewhere.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by felgall View Post
    Regardless of the "protection" built into such calls you should always validate the data if it was input by a person or sanitize it if it was obtained from elsewhere.
    While I can understand validating, eg, making sure the data is the type I expect, isn't the point of PDO to eliminate the need to sanitize? If not, then whats the point? Its more complicated, and more annoying, to use then straight queries.

  • #8
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    http://php.net/manual/en/pdo.prepare.php
    Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.
    Last edited by kbluhm; 01-27-2012 at 09:26 PM.

  • #9
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I saw that line as well kbluhm, but I wasn't sure if that means that execute actually does the sanitizing or not, and if so, as what (I assume string by default).

  • #10
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    bind and execute don't do any sanitizing. All using prepare/bind does is keeps the data separate from the SQL so as to prevent SQL injection. It doesn't do anything to check that the data doesn't contain garbage. At best you will have the database call crash if the data is garbage (eg. trying to put text into a numeric field) but for most cases the garbage will just be processed as if it were valid (eg inserting a number into a name field that you didn't validate or sanitize to make sure it at least looks like a name).
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #11
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    If type-casting is considered sanitizing, then you may look into parameter binding using PDOStatement::bindParam() as Fou-Lu had alluded:
    http://php.net/manual/en/pdostatement.bindparam.php

  • #12
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    So far, I have been using bindParam/bindValue. I was just trying to get a better understanding of how PDO works to better utilize it.

    And felgall, perhaps I don't understand what sanitize means, but isn't sanitizing data the process of making sure you escape/process the data to prevent malicious queries? And isn't that different from validating? As I said above, I do validate, but if PDO isn't meant to sanitize, I don't see what purpose it has. In fact, kbluhm copied from the PHP manual where it says PDO does the work so you don't need to manually quote, which is sanitizing, lest I am mistaken.

    And by way way felgall, if you try to put a word into a numeric field which you tell PDO should be numeric, it will turn it to 0 (same as intval). And if you try to insert words into a numeric field and don't tell PDO it should be numeric, it still converts it to 0. I even tried doing a malicious insert and it still just changed it to a 0 on insert.

    I don't mean to be argumentative, but I feel like I'm being advice/answers contradictory to what I got here just a few months back when I first asked about best methods to prevent injection.

    So please, correct me if I'm wrong. Isn't sanitizing the process to prevent malicious attacks (adding slashes, etc), and isn't validating the process of making sure the data you are receiving is in the format you want it in? And if PDO doesn't sanitize, whats the point in using it? Obviously its a lot more then just typecasting, because it so far seems pretty smart about figuring out what kind of data to expect, then making sure the data I give it is of that type, or converting it to that type.

  • #13
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    You are getting sanitizing and escaping mixed up. One is done on input and the other on output and their purposes are completely different.

    Sanitizing is stripping out characters that are invalid for the particular type of data it is supposed to be whereas validating means actually rejecting the entire field if it contains characters that are invalid. They are very closely related in that after performing either the resulting data is reasonable. See http://au.php.net/manual/en/filter.filters.sanitize.php for a selection of sanitizing filters that are built into PHP.

    Both validating and sanitizing are INPUT functions that are performed to make sure that the data being processed makes sense.

    Escaping is an output function that you perform on valid data in order to prevent that data being misinterpreted as a command. Using prepare statements in SQL keeps the data separate from the SQL and so avoids the need to escape the data in that instance. It does not have any effect on your input processing as sending data to SQL is an output function. Processing anything returned from the call is then an input function and sanitization should be considered as it prevents invalid data that somehow did manage to get into the database from doing any further harm in subsequent processing.

    mysql_real_escape_string() and html_specialchars() are both output escaping functions intended to take valid data and to escape anything in that valid data that might be misinterpreted as SQL or HTML respectively.

    A lot of people get their input and output processing mixed up as most of the examples they see when learning are small and often omit the input functions as provided that the data is valid no input functions to validate and sanitize are required whereas the output functions to escape the data are still required where the data is being jumbled into the command. In any real world program the escaping and sanitizing ensure that the data you are processing is reasonable - since most attempts to do harm involve data that would be considered invalid those attempts get blocked as a side effect of validation. If the validation or sanitizing wasn't there then keeping the data separate (eg. prepare/bind) or escaping the data will prevent the attack having the effect the attacker wants but it will still allow junk to be loaded into your database. The input processing to validate or sanitize the data prevents the junk getting that far.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    Dormilich (01-28-2012)

  • #14
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,538
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I apologize. I guess I've been using the terms wrong this whole time, and I suppose its led to be a bit more brusk then I should have been.

  • #15
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,395
    Thanks
    13
    Thanked 353 Times in 349 Posts
    too bad I’m way too late for this thread …
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •