| Clean PDO-MySQL Statement [message #177682] |
Fri, 13 April 2012 11:30  |
Alexandra Herzog
Messages: 4
Registered: April 2012
Karma: 0
|
Junior Member |
|
|
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']." ".$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
|
|
|
|
| Re: Clean PDO-MySQL Statement [message #177683 is a reply to message #177682] |
Fri, 13 April 2012 12:42  |
M. Strobel
Messages: 386
Registered: December 2011
Karma: 0
|
Senior Member |
|
|
Path: textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!193.141.40 .65.MISMATCH!npeer.de.kpn-eurorings.net!npeer-ng0.de.kpn-eurorings.net!fu-b erlin.de!uni-berlin.de!not-for-mail
From: "M. Strobel" <sorry_no_mail_here(at)nowhere(dot)dee>
Newsgroups: comp.lang.php
Subject: Re: Clean PDO-MySQL Statement
Date: Fri, 13 Apr 2012 14:42:26 +0200
Lines: 49
Message-ID: <9uql9iFoj0U1(at)mid(dot)uni-berlin(dot)de>
References: <9uqh35FovkU1(at)mid(dot)individual(dot)net>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de 7XXBmYjbGyjRMKNR2xh1qAT1f2Vesijq/aZHJ0I3SQHHJHpOg=
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; de; rv:1.9.2.28) Gecko/20120306 Thunderbird/3.1.20
In-Reply-To: <9uqh35FovkU1(at)mid(dot)individual(dot)net>
Xref: textnews.cambrium.nl comp.lang.php:137117
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']." ".$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.
|
|
|
|
| Re: Clean PDO-MySQL Statement [message #177690 is a reply to message #177682] |
Fri, 13 April 2012 16:07 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
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']." ".$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(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]