PDO and user input

[JayStabins]

I know that this has probably come up a lot but a quick search didn't give me the answer I am looking for.. haha.

 

Anyway, I want to know what is really needed to do to user input while dealing with my database.

I know that using straight sql is a no go and wouldn't.  Back years ago I worked for a small marketing company using MSSql and we handled user input way different than is happening now and just want to make sure I am not doing anything stupid.

 

From everything I am reading as long as I am using prepared statements in PDO I should really have no problem with SQL ingection.

 

A good site I got a kick out of while doing my research is here

http://bobby-tables.com/php.html

 

So as long as I am doing prepared named queries I should have no problem with SQL injection issues? 

$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
$stmt = $dbh->prepare('UPDATE people SET name = :new_name WHERE id = :id');
$stmt->execute( array('new_name' => $name, 'id' => $id) );

While this would be great news I can't believe it.

Now I know that this does not protect against DISPLAYING information from the database which is a separate issue, using something like 

htmlspecialchars

should always be used when displaying based on your medium.

 

 

So what do you do when dealing with user input?

If you are using prepared statements do you bother doing any type of check?  If so, what are we doing with PDO data?

 

If you are still using something what are we doing? 

 

if not I am just as happy and would love to hear if so because it would really make my life a lot eaiser!! 

 

Thanks in advance!

[NomadicJosh]

A few things I ask about each input when designing the database and forms:

 

1. Is it a string?

2. If it is a string, how many characters?

3. Should I do a character length check?

4. Is it an integer?

 

If it is an integer, I usually check for it by doing something like this:

(int)$variable;

On output, I usually use a function like this:

    function _h($str) {
        return htmlspecialchars($str,ENT_QUOTES,'UTF-8');
    }

[kicken]

So as long as I am doing prepared named queries I should have no problem with SQL injection issues? 

[snip]

While this would be great news I can't believe it.

Pretty much. By using prepared statements you are keep the data entirely separate from the query text so there is no possibility of the two getting confused. Unlike what is commonly believed, the data does not just get escaped and inserted into the SQL where the placeholder is. Rather, what happens is the database will see that placeholder, then refer to a separate list of values to find the value which should be used in place of that placeholder.

 

 

If you are using prepared statements do you bother doing any type of check?  If so, what are we doing with PDO data?

You still will want to do all your normal validations depending on the data. If the input is supposed to be a date, validate it to make sure it really is a date for example. Same for emails, urls, numbers, etc. The prepared statements just make it so the data won't cause and problems with the interpretation of your query. It's still up to you to keep out the bad/invalid data.

Edited October 20, 2013 by kicken

[vinny42]

Prepared statements are very safe, but it is not a magic bullet. A prepared statement is "prepared" without any data in it, which means that the database cannot use the data to compile it's plan, which means the databae may decide that it shouldn't use an index, which can be very bad.

 

A simple solution to this, that apparently ony I know about (given the bizar responses I've received to it) is to create your own databaseclass that just runs queries and returns the output.

If you have that, you can create a method like "runquery($pStrQuery, $pArrParameters)" or if you use PHP's ability to have unlimited parameters: runquery($pStrQuery, $pIntParameter1, $intParameter2, ...)"

The query can then have placeholders like $1, $2, or simply names like ':userid' and a simply loop can force all parameters to be parsed using the escape function, quote them and paste them into the query. that way you can be safe without having to resort to prepared statements.

 

And before people start complaining about integers; most databases will happily accept quoted integers, there is no performance penalty at all.

 

 

 

You still will want to do all your normal validations depending on the data.

 

Where "normal validations" means "the kind of validations that your application does to be able to work with the data.

 

There is no point to validating a date just before you put it into the database, because A) the database will refuse a bad date (unless you are using MySQL, in which case it may or may not work, depending on the IQ of the admin) and B) at that point there is nothing you can do to fix the date anyway.

[kicken]

Prepared statements are very safe, but it is not a magic bullet. A prepared statement is "prepared" without any data in it, which means that the database cannot use the data to compile it's plan, which means the databae may decide that it shouldn't use an index, which can be very bad.

I think this is something that may have been true in the early days of prepared statements, but is less true now that the tech has evolved. For example MySQL will use the first set of parameter values when optimizing the plan. SQL server will do that as well, in addition to storing multiple plans and re-evaluating them if the data statistics change dramatically.

 

 

A simple solution to this, that apparently ony I know about (given the bizar responses I've received to it) is to create your own databaseclass that just runs queries ... safe without having to resort to prepared statements.

This is basically what PDO is when PDO::ATTR_EMULATE_PREPARES is set to true. Under that setup PDO will parse the query and substitute the values itself rather than using the DB's native prepared statements api/syntax.

[vinny42]

 

I think this is something that may have been true in the early days of prepared statements, but is less true now that the tech has evolved. For example MySQL will use the first set of parameter values when optimizing the plan. SQL server will do that as well, in addition to storing multiple plans and re-evaluating them if the data statistics change dramatically.

 

I know PgSQL does this, up to a point, but having combatted this in MySQL less than a year ago I didn't think they would have solved that so quickly.

 

 

This is basically what PDO is when PDO::ATTR_EMULATE_PREPARES is set to true. Under that setup PDO will parse the query and substitute the values itself rather than using the DB's native prepared statements api/syntax. 

 

Interesting, more ways that PDO creates inconsistend API behaviour :-)