Prev: PECL install Xdebug
Next: FIle_PDF
From: "Damian Bursztyn" on 21 Nov 2006 15:00
Hi, im trying to work with MDB2 and hace some problems. Maybe someone can
help me.
First, once i have a mdb2 connection on $mdb2 im trying to make a query
using quote to prevent SQL Injections.

$int = $mdb2->quote('11', 'integer');
$query = 'SELECT someDate, someNumber FROM myTable WHERE someInteger = ' .
$int;
var_dump($query);
$result = $mdb2->exec($query, array('date', 'integer'));

I suppouse that $query should be something like SELECT someDate, someNumber
FROM myTable WHERE someInteger = 11 or maybe SELECT someDate, someNumber
FROM myTable WHERE someInteger = '11' (i preffer this one, can i choose to
put or not the quotes?)

But instead of that the var_dump of query give me something like SELECT
someDate, someNumber FROM myTable WHERE someInteger = Object id #2

How can i fix this?
Because when i make $result = $mdb2->exec($query); an error takes place.

Second, can i use prepare for select querys? i saw that the result object of
prepare only have exec method. If i want to make a query with parameters (in
the where condition) and i want to set types to that parameters. And i want
to work my input data to prevent SQL injections and blind the variable
(after filter it) the the parameter in the query wich is the best way?

I was try to do the prepare with the types and then blinds the variables but
i dont know if this makes the quote using the parameter type or not.

Thanks for your help.
From: brian on 21 Nov 2006 15:11
Damian Bursztyn wrote:
> Hi, im trying to work with MDB2 and hace some problems. Maybe someone can
> help me.
> First, once i have a mdb2 connection on $mdb2 im trying to make a query
> using quote to prevent SQL Injections.
>
> $int = $mdb2->quote('11', 'integer');
> $query = 'SELECT someDate, someNumber FROM myTable WHERE someInteger = ' .
> $int;
> var_dump($query);
> $result = $mdb2->exec($query, array('date', 'integer'));
>
> I suppouse that $query should be something like SELECT someDate, someNumber
> FROM myTable WHERE someInteger = 11 or maybe SELECT someDate, someNumber
> FROM myTable WHERE someInteger = '11' (i preffer this one, can i choose to
> put or not the quotes?)
>
> But instead of that the var_dump of query give me something like SELECT
> someDate, someNumber FROM myTable WHERE someInteger = Object id #2
>
> How can i fix this?
> Because when i make $result = $mdb2->exec($query); an error takes place.
>
> Second, can i use prepare for select querys? i saw that the result
> object of
> prepare only have exec method. If i want to make a query with parameters
> (in
> the where condition) and i want to set types to that parameters. And i want
> to work my input data to prevent SQL injections and blind the variable
> (after filter it) the the parameter in the query wich is the best way?
>
> I was try to do the prepare with the types and then blinds the variables
> but
> i dont know if this makes the quote using the parameter type or not.
>
> Thanks for your help.
>

The exec() is operating on the prepared statement. If you're only
SELECTing rows, add MDB2_PREPARE_RESULT to the prepare() call. If your
statement will be modifying the table in any way, use MDB2_PREPARE_MANIP.

Try:

$your_integer = intval($whatever);

$query = 'SELECT someDate, someNumber FROM myTable WHERE someInteger = ?';

$stmnt = $mdb2->prepare($query, Array('integer'), MDB2_PREPARE_RESULT);

// note that you shouldn't really echo this out on a production site
if (MDB2::isError($stmnt))
{
die($stmnt->getUserInfo());
}

$result = $stmnt->execute(Array($your_integer));

if (PEAR::isError($result))
{
die($result->getUserInfo());
}

/* call $mdb2->setFetchMode (MDB2_FETCHMODE_ASSOC) beforehand
* or use $result->fetchRow(MDB2_FETCHMODE_ASSOC)
*/
while ($row = $result->fetchRow())
{
...
}

brian
From: Lorenzo Alberton on 21 Nov 2006 15:48
Hi Damian,

> Hi, im trying to work with MDB2 and hace some problems. Maybe someone can
> help me.
> First, once i have a mdb2 connection on $mdb2 im trying to make a query
> using quote to prevent SQL Injections.
>
> $int = $mdb2->quote('11', 'integer');
> $query = 'SELECT someDate, someNumber FROM myTable WHERE someInteger = ' .
> $int;
> var_dump($query);
> $result = $mdb2->exec($query, array('date', 'integer'));

exec() should be used for manipulation queries
(INSERT / UPDATE / ...). If you want to SELECT
some fields, use query():

http://pear.php.net/manual/en/package.database.mdb2.intro-query.php

If you want to query and fetch the data in one shot, you can
use one of queryAll(), queryCol(), queryRow(), queryOne():

http://pear.php.net/manual/en/package.database.mdb2.intro-fetch.php

> I suppouse that $query should be something like SELECT someDate, someNumber
> FROM myTable WHERE someInteger = 11 or maybe SELECT someDate, someNumber
> FROM myTable WHERE someInteger = '11' (i preffer this one, can i choose to
> put or not the quotes?)

MySql accepts the quotes around integer values,
but all the other DBMS don't, so it's better if
you don't put quotes around integers in any case.


> Second, can i use prepare for select querys?

absolutely, yes.

> i saw that the result object of
> prepare only have exec method. If i want to make a query with parameters
> (in
> the where condition) and i want to set types to that parameters. And i want
> to work my input data to prevent SQL injections and blind the variable
> (after filter it) the the parameter in the query wich is the best way?

http://pear.php.net/manual/en/package.database.mdb2.intro-execute.php


> i dont know if this makes the quote using the parameter type or not.

if you use prepared queries, and pass an array
containing the datatypes of your parameters, then
quoting is done automatically where necessary.

> Thanks for your help.

you're welcome.

BTW: all of your questions are covered by the docs:
http://pear.php.net/manual/en/package.database.mdb2.php
so you may want to read them to learn how to use MDB2.

Best regards,
--
Lorenzo Alberton
http://pear.php.net/user/quipo
___________________________________________________________________
Quipo Free Internet - 2 email, 150 Mb di spazio web e molto di pi?.
ADSL, Hardware & Software Online Store
 | 
Pages: 1
Prev: PECL install Xdebug
Next: FIle_PDF