Two PDO questions

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Two PDO questions

Post by rhecker »

I am converting a web application for PDO for the first time. I have two questions.

1. It was my understanding that when using prepared statements I would no longer need to use mysql_real_escape_string but my experimenting reveals that I must also use bindValue. True?

2. It is very unlikely that two different users would be updating the same table row at the same time, so I am inclined to not bother using transactions. Some of the examples and tutorials I've seen wrap each execute within a transaction, and some don't. I'm not completely clear regarding when I need to use transactions. Advice?
xtiano77
Forum Commoner
Posts: 72
Joined: Tue Sep 22, 2009 10:53 am
Location: Texas

Re: Two PDO questions

Post by xtiano77 »

I haven't had any experience with transactions yet, but as far as your first question the answer is true. You need to use either "bindValue" or "bindParameter" depending on how the values will be applied to the SQL statement. If the value to be used will come from an argument or a variable then you should use "bindParameter", otherwise you should use "bindValue". Example:

Code: Select all

$sql = "SELECT * 
           FROM databasename.tablename 
           WHILE parameter = :value";
$statement = $db -> prepare($sql);
# This should assign the value in $argument to ":value" while making sure that the value is properly escaped.
$statement -> bindParam(":value", $argument);
$statement -> execute( );
What you should avoid is doing something like this:

Code: Select all

$sql = "SELECT *
           FROM databasename.tablename 
           WHILE parameter = '" . $argument "'";
$statement = $db -> prepare($sql);
// rest of your code here...
or

Code: Select all

$result = $db -> query($sql);
// rest of your code here...
I could be a little bit off since I am not at my home computer right now and don't have access to my server, but I believe that the above should answer your question. Please note that prepared statements do not replace client side or server validation of the submitted input. Hope this helps.

http://www.php.net/manual/en/pdo.prepare.php
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Two PDO questions

Post by twinedev »

A few notes from my experience with PDO.

For the bind parameter, you can do this once and then run a loop and not have to re-bind it. When the execute runs, it will use what is stored in the variable at that time...

Code: Select all

$aryData = array(1=>"Greg", 3=>"Max", 8=>"Ricky");

$key = 0;
$value = '';
$pdoUpdate = $db->prepare('UPDATE tblWhatever SET myField=:fieldvalue WHERE keyField=:keyfield');
$pdoUpdate->bindParam(':fieldvalue',$key);
$pdoUpdate->bindParam(':keyfield',$val);

foreach($aryData as $key=>$val) {
    $pdoUpdate->execute();
}
If you are doing several PDO oobjects, make sure after you are done with any $pdoObject->fetch(); section, do a $pdoObject->closeCursos(); This was a tough one to figure out when we switching to using PDO, we didn't know and left them all out there hanging so to speak, so pages would start giving us WSOD (white screen of death, what we called it cause you just got a empty browser window)

Trying to trace those down... a bt of a pain, cause it would say the fault would happen on say line 1032, which was a PDO execute. However the issue was that OTHER pdo objects were open and you had to back trace through the code to find previous ones left open. (On top of this, the way the Database handler was written, they mistakenly created a NEW connection for every build of PDO object. Brought a brand new production web server to a crawl once a high traffic site was taken live on it. Was a mess to learn the hard way for sure.

Another area (well 2) that threw us off: SELECT * FROM tblWhatever WHERE Key IN (3,6,7) can't be done via PDO (unless you know HOW many options in advance, pretty darn messy. For these still ended up putting the values in the prepare:

Code: Select all

$aryKeys = array(3,6,7);
$pdoValues = $db->prepare('SELECT * FROM tblWhatever WHERE Key IN ('.implode(',',$aryKey).')');
Another area where we had to code the variables into the prepare was the use for LIMIT 3,5 .... Cannot do:

Code: Select all

$pdoValues = $db->prepare('SELECT * FROM tblWhatever LIMIT :start, :len ')';
Both these examples the worry about injection were not much of issues as the they ended up being integers, so you could type cast the value as an int, and no injection issues two examples in the same code:

Code: Select all

$aryKeys = array(3,6,7);
$intStart = (isset($_GET['start'] && (int)$_GET['start'] > 0) ? (int)$_GET['start'] : 0;
$intLength = (isset($_GET['perpage'] && (int)$_GET['perpage'] >= 5) ? (int)$_GET['perpage'] : 10;
// NOTE, the above says per page needs to be at least 5, and if not set (or attempted injection) default to 10

$pdoValues = $db->prepare('SELECT * FROM tblWhatever WHERE Key IN ('.implode(',',(int)$aryKey).') LIMIT '.$intStart.','.$intLength.);
Just some tips.

-Greg
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: Two PDO questions

Post by rhecker »

Thank you both!

Greg, I really appreciate the heads up regarding these potential issues. I will carefully review your points. Thanks much.
Post Reply