Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #5511 > unrolled thread

Clean PDO-MySQL Statement

Back to article view | Back to comp.lang.php

Contents

  Clean PDO-MySQL Statement Alexandra Herzog <herzog.alex@arcor.de> - 2012-04-13 13:30 +0200
    Re: Clean PDO-MySQL Statement "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2012-04-13 14:42 +0200
    Re: Clean PDO-MySQL Statement Jerry Stuckle <jstucklex@attglobal.net> - 2012-04-13 12:07 -0400

#5511 — Clean PDO-MySQL Statement

Hi everyone,

I am using the PDO with MySQL for the first time and created a statement. Besides from individual input validation (which I always do), I would like to know, if you consider the following to be a clean statement:

That's what I figured from googled examples:

include ("pdoconnect.php");
$stmt = $dbh->prepare("SELECT firstname, name FROM telephonebook WHERE from_work = :workstatus ORDER BY :mywish ASC");
$stmt->bindParam(':workstatus', $n, PDO::PARAM_INT);
$stmt->bindParam(':mywish', $value, PDO::PARAM_STR);
$n = 1; $order = "firstname";
$stmt->execute();

echo "<u>".$stmt->rowCount()."</u>\r\n";
while ($row = $stmt->fetch())
  echo $row['firstname']."&nbsp;".$row['name']."<br>";
$stmt->closeCursor();
$dbh = null;


in pdoconnect.php:
-----------------------
try
  { $dbh = new PDO('mysql:host=host1.myhost.com;dbname=mydb1', alex, mypass);
    foreach ($dbh->query('SELECT * from FOO') as $row)
      { print_r($row); }
  }
    catch(PDOException $e)
      { print "Database connection error!<br/>";
        die();
      }

I tried to prevent SQL injection methods by specifying PDO::PARAM*, and closing the statement and connection properly.

Is this a correct example? Or should I improve something?

Any hints greatly appreciated, since I am about to change all my scripts to this :-)
Thanks, Alex

[toc] | [next] | [standalone]

#5512

Am 13.04.2012 13:30, schrieb Alexandra Herzog:
> Hi everyone,
> 
> I am using the PDO with MySQL for the first time and created a statement. Besides from individual input validation (which I always do), I would like to know, if you consider the following to be a clean statement:
> 
> That's what I figured from googled examples:
> 
> include ("pdoconnect.php");
> $stmt = $dbh->prepare("SELECT firstname, name FROM telephonebook WHERE from_work = :workstatus ORDER BY :mywish ASC");

prepare will give you bool(false) if something is wrong. If you do not test for this
your $stmt->bindParam() can fail. Either always test it, wrap the call, or use
exception mode (see below).


> $stmt->bindParam(':workstatus', $n, PDO::PARAM_INT);
> $stmt->bindParam(':mywish', $value, PDO::PARAM_STR);
> $n = 1; $order = "firstname";
> $stmt->execute();
> 
> echo "<u>".$stmt->rowCount()."</u>\r\n";
> while ($row = $stmt->fetch())
>   echo $row['firstname']."&nbsp;".$row['name']."<br>";
> $stmt->closeCursor();
> $dbh = null;
> 
> 
> in pdoconnect.php:
> -----------------------
> try
>   { $dbh = new PDO('mysql:host=host1.myhost.com;dbname=mydb1', alex, mypass);

Those making use of the OO goodies would define their own wrapper class where they
put all the db helpers, like this:

class Dbman extends PDO {
    function __construct($dburl) {
        parent::__construct($dburl);
        # $this->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
        # $this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );
    }
}

Errormode exception tells PDO to thow an exception on errors. This makes it easy to
frame your code with try - catch without testing every single database request.

A lot more can be said to this subject...

/Str.

[toc] | [prev] | [next] | [standalone]

#5519

On 4/13/2012 7:30 AM, Alexandra Herzog wrote:
> Hi everyone,
>
> I am using the PDO with MySQL for the first time and created a statement. Besides from individual input validation (which I always do), I would like to know, if you consider the following to be a clean statement:
>
> That's what I figured from googled examples:
>
> include ("pdoconnect.php");
> $stmt = $dbh->prepare("SELECT firstname, name FROM telephonebook WHERE from_work = :workstatus ORDER BY :mywish ASC");
> $stmt->bindParam(':workstatus', $n, PDO::PARAM_INT);
> $stmt->bindParam(':mywish', $value, PDO::PARAM_STR);
> $n = 1; $order = "firstname";
> $stmt->execute();
>
> echo "<u>".$stmt->rowCount()."</u>\r\n";
> while ($row = $stmt->fetch())
>    echo $row['firstname']."&nbsp;".$row['name']."<br>";
> $stmt->closeCursor();
> $dbh = null;
>
>
> in pdoconnect.php:
> -----------------------
> try
>    { $dbh = new PDO('mysql:host=host1.myhost.com;dbname=mydb1', alex, mypass);
>      foreach ($dbh->query('SELECT * from FOO') as $row)
>        { print_r($row); }
>    }
>      catch(PDOException $e)
>        { print "Database connection error!<br/>";
>          die();
>        }
>
> I tried to prevent SQL injection methods by specifying PDO::PARAM*, and closing the statement and connection properly.
>
> Is this a correct example? Or should I improve something?
>
> Any hints greatly appreciated, since I am about to change all my scripts to this :-)
> Thanks, Alex

No, it will not work.  You cannot use a bind parameter as a column name 
(i.e. in the ORDER BY clause).  You can only use it where you have a value.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web