[PHP-DB] PDO::quote is only *theoretically* safe ?

[Tobias Petry]

The documentation for PDO::quote [0] says that a processed string is 
"theoretically safe to pass into an SQL statement". Understandably, prepared 
statements should be preferred when possible. But I need to change some stuff 
where integrating them is impossible, and some values must be securely embedded 
into a query string.

There's only one stated problem of PDO::quote that could result in a SQL 
injection: When the charset has not been set for the connection. But as warned 
by the documentation, this will be guaranteed.

Neither mysql_real_escape_string [1] nor pg_escape_literal [2] which escape 
values for embedding into SQL query strings have a note about being only 
*theoretically* safe. Only PDO::quote has such a warning.

* Is there any reason PDO::quote should be less safe than 
mysql_real_escape_string or pg_escape_literal for embedding values in SQL 
queries?
* Is it just written in a very security-centric way so anyone sticks to 
prepared statements?

Although e.g. Wordpress is not known for the cleanest code, 
mysql_real_escape_string has been used for years without any known problems.

[0] https://www.php.net/manual/en/pdo.quote.php
[1] https://www.php.net/manual/en/function.mysql-real-escape-string
[2] https://www.php.net/manual/en/function.pg-escape-literal.php

[PHP-DB] Re: [External] : [PHP-DB] PDO to Oracle db and secure external password store aka wallet for authentication

[Christopher Jones]

On 28/9/21 6:59 pm, Mathias Zarick wrote:

Hi there,

I am wondering how it would be possible to use authentication using a secure 
external password store aka wallet with PDO OCI.

See Oracle external authentication and OCI_CRED_EXT in the underground php 
oracle manual.

and

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-803496D2-19C7-4F02-94EC-C13EDD8FB17B
(jump to chapter 3.2.9)


Following works fine with oci8:
$conn = oci_connect('/', '', $db, 'UTF8',OCI_CRED_EXT);

note the OCI_CRED_EXT!

But how can pass that option to a PDO OCI connection?
Following might be close, but does not work:

   $opt = [
   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
   PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM,
   'session_mode' => OCI_CRED_EXT,

];

   try{
   $conn = new PDO("oci:dbname=".$db.';charset=UTF8', "/","", $opt);
   }
   catch(PDOException $e){
   echo ($e->getMessage());
   }

Is it possible at all?


You can see in the PDO_OCI source code there is no reference to the OCI_CRED_EXT constant which is needed to tell the Oracle client libraries to use 
external authentication.


PR's are welcome.

For the moment stick with OCI8.  In general, native drivers (e.g OCI8) will be 
better than any of the PDO abstractions.

Chris




Thanks in Advance
Mathias Zarick


--
https://twitter.com/ghrd

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO to Oracle db and secure external password store aka wallet for authentication

[Mathias Zarick]

Hi there,

I am wondering how it would be possible to use authentication using a secure 
external password store aka wallet with PDO OCI.

See Oracle external authentication and OCI_CRED_EXT in the underground php 
oracle manual.

and

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-803496D2-19C7-4F02-94EC-C13EDD8FB17B
(jump to chapter 3.2.9)


Following works fine with oci8:
$conn = oci_connect('/', '', $db, 'UTF8',OCI_CRED_EXT);

note the OCI_CRED_EXT!

But how can pass that option to a PDO OCI connection?
Following might be close, but does not work:

  $opt = [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM,
  'session_mode' => OCI_CRED_EXT,

];

  try{
  $conn = new PDO("oci:dbname=".$db.';charset=UTF8', "/","", $opt);
  }
  catch(PDOException $e){
  echo ($e->getMessage());
  }

Is it possible at all?

Thanks in Advance
Mathias Zarick

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

Your answer in the part of SAP HANA (J)ODBC driver not supporting named
parameters actually makes sense. However I don't understand why would there
be a difference between PDO and regular odbc (odbc_connect, etc.) commands
since they're obviously using the same driver.

My testing machine is on Windows and when using raw odbc commands, I pass
in the driver parameter 'HDBODBC32'. If I use PDO, I have to pass in the
ODBC DSN, which is created in the Windows ODBC management tool. I double
checked and the driver in use is the same. So what bugs me is why would raw
odbc commands accept parameters with ? and $ notations but PDO doesn't
accept neither (well, query executes but doesn't return any results).

I'd like to try and debug the PDO module but unfortunately that's beyond my
knowledge and time frame for the moment :) Perhaps any advice on this,
debugging PDO? If nothing else, using PDO would at least unify the database
access accross my application and it does seem cleaner to use.

On Tue, Jan 26, 2016 at 3:50 PM, Lester Caine  wrote:

> On 26/01/16 19:06, Alko Kotalko wrote:
> > I've tried all the notations with PDO as well and none of them work with
> > SAP HANA. It works with MySQL though. So I presume that there is either a
> > bug in PDO driver or there is some mismatch between PDO and SAP HANA.
>
> Firebird does not support named parameters so we stick with ?
> parameters. Not sure on SAP HANA, but a quick google gives
> "The SAP HANA JDBC driver doesn't support named parameters" and I would
> anticipate that the ODBC driver probably has the same problem, but the
> simpler ordered array of parameters should work ...
>
> There WAS a document about just what combinations HAVE been tested and
> worked, but I'm not even sure now where it as created :(
>
> From the generic ODBC driver -
> http://php.net/manual/en/function.odbc-prepare.php ...
> Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
> stored procedures that accept parameters of type IN, INOUT, and OUT as
> defined by the ODBC specification. However, the Unified ODBC driver
> currently only supports parameters of type IN to stored procedures.
>
> It is some time since I used the ODBC interface, but I think PDO_odbc
> still uses the generic ODBC inerface and this does have it's own
> restrictons based on what platform you are using. It may be worth trying
> the generic ODBC interface and see if this works any differently. The
> ODBC driver uses the same style of working as the Firebird/Interbase
> driver for binding variables and that does work.
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:

> Hi,
> 
> I have a working connection from PHP to SAP HANA through PDO and regular
> ODBC commands.
> 
> The issue is that through PDO I can not get any prepared statements to
> work. None of the notations (?, $, :) work. The response from the server
> (fetch) gets me empty field values for all selected columns and if I try
> fetchAll the PHP script runs out of memory.
> 
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.
> 
> For example:
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> 
> However it is not ideal. I would like to know if anyone has any experience
> with SAP HANA and I can offer my help and participation in order to debug
> the possible problems with PDO<->HANA connectivity issues (in regards to
> prepared statements).
> 
> BR Aleš


Pardon me for asking, but shouldn't this line..

"SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"

be

"SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "

or like this

"SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  "

?

I have found that letting php create the string without the quotes and periods 
in a SQL statement can cause issues.
Don't ask me why it causes issues, I couldn't tell you :)  Just my experience. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Jeff]

Greetings,

On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
>> Hi,
>>
>> I have a working connection from PHP to SAP HANA through PDO and regular
>> ODBC commands.
>>
[...snipped...]

>>
>> For example:
>> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.

the "$" character within a php string enclosed with quotes (") is
special. you /might/ have some success if you enclose the string in
single quotes (') instead. also personally I prefer named parameters
(:foo) or even "foo=?" (question mark) rather than $1...n etc.

[...snipped...]

regards,
J


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 13:10, Alko Kotalko wrote:
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.

This is the key to your problem ...

The PDO generic process does not support named parameters directly, so
the ? format duplicating the multiple use of a named parameter is
required ... if the driver actually supports that?

Does your setup work if the SQL is

$stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
col3=?");
$stmt->bindParam(1, $S1);
$stmt->bindParam(2, $S2);
$stmt->bindParam(3, $S1);

On some drivers the named parameters have to be converted to '?' format,
but I can't find the notes now on what combination works and what does't :(

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

I've tried all the notations with PDO as well and none of them work with
SAP HANA. It works with MySQL though. So I presume that there is either a
bug in PDO driver or there is some mismatch between PDO and SAP HANA.

The queries get executed but the returned result (if I fetch one) is an
associative array with empty values. If I fetch all, PHP runs out of memory
but the arrays in array are the same, keys without values.

On Tue, Jan 26, 2016 at 9:17 AM, Lester Caine  wrote:

> On 26/01/16 13:10, Alko Kotalko wrote:
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
>
> This is the key to your problem ...
>
> The PDO generic process does not support named parameters directly, so
> the ? format duplicating the multiple use of a named parameter is
> required ... if the driver actually supports that?
>
> Does your setup work if the SQL is
>
> $stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
> col3=?");
> $stmt->bindParam(1, $S1);
> $stmt->bindParam(2, $S2);
> $stmt->bindParam(3, $S1);
>
> On some drivers the named parameters have to be converted to '?' format,
> but I can't find the notes now on what combination works and what does't :(
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

It shouldn't be like that because I'm preparing a statement, which would
later have had parameters passed to. I'm not trying to concatenate a string.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers 
wrote:

> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
>
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any
> experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
>
>
> Pardon me for asking, but shouldn't this line..
>
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
>
> be
>
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
>
> or like this
>
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND
> col3='".$1."'  "
>
> ?
>
> I have found that letting php create the string without the quotes and
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my
> experience.
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

This (my code) actually works so it's not part of the problem. The problem
is that I can NOT use the colon notation for named parameters, even though
that's my goal :) If I use the colon notation with MySQL (PDO) it works
fine. With ODBC and SAP HANA database it doesn't. I have to use either ? or
$ notation.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 9:01 AM, Jeff  wrote:

> Greetings,
>
> On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> > On Jan 26, 2016, at 7:10 AM, Alko Kotalko 
> wrote:
> >
> >> Hi,
> >>
> >> I have a working connection from PHP to SAP HANA through PDO and regular
> >> ODBC commands.
> >>
> [...snipped...]
>
> >>
> >> For example:
> >> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
>
> the "$" character within a php string enclosed with quotes (") is
> special. you /might/ have some success if you enclose the string in
> single quotes (') instead. also personally I prefer named parameters
> (:foo) or even "foo=?" (question mark) rather than $1...n etc.
>
> [...snipped...]
>
> regards,
> J
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

Oh ok, thanks for the clarification. Sorry for the noise.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



On Jan 26, 2016, at 1:07 PM, Alko Kotalko  wrote:

> It shouldn't be like that because I'm preparing a statement, which would 
> later have had parameters passed to. I'm not trying to concatenate a string.
> (Sorry, forgot to reply to all before)
> 
> On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers  
> wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
> 
> 
> Pardon me for asking, but shouldn't this line..
> 
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
> 
> be
> 
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
> 
> or like this
> 
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  
> "
> 
> ?
> 
> I have found that letting php create the string without the quotes and 
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my experience.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 19:06, Alko Kotalko wrote:
> I've tried all the notations with PDO as well and none of them work with
> SAP HANA. It works with MySQL though. So I presume that there is either a
> bug in PDO driver or there is some mismatch between PDO and SAP HANA.

Firebird does not support named parameters so we stick with ?
parameters. Not sure on SAP HANA, but a quick google gives
"The SAP HANA JDBC driver doesn't support named parameters" and I would
anticipate that the ODBC driver probably has the same problem, but the
simpler ordered array of parameters should work ...

There WAS a document about just what combinations HAVE been tested and
worked, but I'm not even sure now where it as created :(

>From the generic ODBC driver -
http://php.net/manual/en/function.odbc-prepare.php ...
Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
stored procedures that accept parameters of type IN, INOUT, and OUT as
defined by the ODBC specification. However, the Unified ODBC driver
currently only supports parameters of type IN to stored procedures.

It is some time since I used the ODBC interface, but I think PDO_odbc
still uses the generic ODBC inerface and this does have it's own
restrictons based on what platform you are using. It may be worth trying
the generic ODBC interface and see if this works any differently. The
ODBC driver uses the same style of working as the Firebird/Interbase
driver for binding variables and that does work.

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

Hi,

I have a working connection from PHP to SAP HANA through PDO and regular
ODBC commands.

The issue is that through PDO I can not get any prepared statements to
work. None of the notations (?, $, :) work. The response from the server
(fetch) gets me empty field values for all selected columns and if I try
fetchAll the PHP script runs out of memory.

ODBC commands actually work with the ? and colon ($) notations. But not
with colon (:). I suppose this is due to the lack of named parameters
support in ODBC commands (haven't actually confirmed that though). The $
notation brings me the closest to named parameters because a specific
number can be repeated.

For example:
"SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.

However it is not ideal. I would like to know if anyone has any experience
with SAP HANA and I can offer my help and participation in order to debug
the possible problems with PDO<->HANA connectivity issues (in regards to
prepared statements).

BR Aleš

[PHP-DB] PDO::ATTR_TIMEOUT with unixODBC not working the way I expect?

[Rob Crowell]

I'm connecting to a closed-source database called Vertica via unixODBC and
PDO.  Everything is working great but I am having trouble getting
PDO::ATTR_TIMEOUT attribute working when I create a connection via:

$this-conn = new PDO($dsn, $user, $password,
  array(PDO::ATTR_DEFAULT_FETCH_MODE =
PDO::FETCH_ASSOC,
PDO::ATTR_TIMEOUT = 2));

or

$this-conn-setAttribute(PDO::ATTR_TIMEOUT, 2);

I also found this bug from 2008 (https://bugs.php.net/bug.php?id=45287),
which contains a patch for this issue.  But browsing the php mirror on
github, I don't see this patch applied:
https://github.com/php/php-src/blob/master/ext/pdo_odbc/odbc_driver.c#L339-L352

Does anyone have any experience or advice with how I could implement a
database connection timeout without this patch?  Thanks!

--Rob

[PHP-DB] PDO Connection problem

[Jim Giner]

History:
I'm trying to help a friend who is hosting his domain with the same 
company that I use.  I've been using this company for several years and 
have used a certain 'connection' script all the time.  Part of it looks 
like this:


$host=mysql:host=mydomain.com;dbname=$sc_dbname;charset=utf8;
$uid = uid;
$pswd = pswd;
Try
{
$mysql = new PDO($host,$uid,$pswd,$db_options);
}
.

So - when I tried to provide this template to my friend (who is new to 
all of this) we went thru days of emails trying to make sure everything 
was setup correctly but could never get a connection using the above 
code.  Finally last night, after reviewing how my 'old' mysql interface 
connection worked, I experimented with the above changing my host= from 
my domain name to simply 'localhost'.  Voila - it worked for him.  It 
also worked for my site.


Here's my question:  What would make by friend's account not work when 
referencing a true domain name in the host= attribute?  I'm assuming 
that our (shared) provider is setting up his many accounts  servers the 
same way, but I could be wrong.  And of course, I don't have a clue 
about what makes any of this work - I simply follow 
instructions/guidance I get from manuals and searches until I get things 
to work.  That's how I got his account to finally work, but I'd love to 
have an idea why it now does.



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO Connection problem

[Aziz Saleh]

On Fri, Jan 10, 2014 at 10:13 AM, Jim Giner jim.gi...@albanyhandball.comwrote:

 History:
 I'm trying to help a friend who is hosting his domain with the same
 company that I use.  I've been using this company for several years and
 have used a certain 'connection' script all the time.  Part of it looks
 like this:

 $host=mysql:host=mydomain.com;dbname=$sc_dbname;charset=utf8;
 $uid = uid;
 $pswd = pswd;
 Try
 {
 $mysql = new PDO($host,$uid,$pswd,$db_options);
 }
 .

 So - when I tried to provide this template to my friend (who is new to all
 of this) we went thru days of emails trying to make sure everything was
 setup correctly but could never get a connection using the above code.
  Finally last night, after reviewing how my 'old' mysql interface
 connection worked, I experimented with the above changing my host= from my
 domain name to simply 'localhost'.  Voila - it worked for him.  It also
 worked for my site.

 Here's my question:  What would make by friend's account not work when
 referencing a true domain name in the host= attribute?  I'm assuming that
 our (shared) provider is setting up his many accounts  servers the same
 way, but I could be wrong.  And of course, I don't have a clue about what
 makes any of this work - I simply follow instructions/guidance I get from
 manuals and searches until I get things to work.  That's how I got his
 account to finally work, but I'd love to have an idea why it now does.


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php


It could be that your MySQL configs -bind-address and skip-networking are
set, so it is not allowing for external connections. My guess (could be
wrong) that using yourdomain.com is the same as making an external call, as
opposed to using localhost (no DNS lookup).

Re: [PHP-DB] PDO Exceptions

[Niel Archer]

 Greetings,
 
 I am new to this list. I have a question about which functions need to be 
 included in a try block. 
 
 Of new PDO, prepare, bindParam, execute, fetch, and query, it 
 seems that bindParam is the only one that throws an exception. So is this the 
 only that needs to be put into a try block?
 
 Thanks,
 
 -KJW
 Proverbs 3:5-6

Are you aware that there is a setting to control whether PDO objects throw
exceptions, warnings, or nothing? By default it does nothing except set
the error code and message.

See http://uk3.php.net/manual/en/pdo.error-handling.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO Exceptions

[Jim Giner]

On 4/25/2013 6:49 AM, Niel Archer wrote:

Greetings,

I am new to this list. I have a question about which functions need to be 
included in a try block.

Of new PDO, prepare, bindParam, execute, fetch, and query, it seems 
that bindParam is the only one that throws an exception. So is this the only that needs to be put into a try block?

Thanks,

-KJW
Proverbs 3:5-6


Are you aware that there is a setting to control whether PDO objects throw
exceptions, warnings, or nothing? By default it does nothing except set
the error code and message.

See http://uk3.php.net/manual/en/pdo.error-handling.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk


And I learn something new today too!

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO Exceptions

[Onatawahtaw]

Greetings,

I am new to this list. I have a question about which functions need to be 
included in a try block. 

Of new PDO, prepare, bindParam, execute, fetch, and query, it seems 
that bindParam is the only one that throws an exception. So is this the only 
that needs to be put into a try block?

Thanks,

-KJW
Proverbs 3:5-6

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 7:07 PM, Matt Pelmear wrote:

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script)
into all of my possible folders under my web root.  NOt exactly an
elegant solution, but with the script, easy to maintain.



Honestly, you would be better off just putting those 6 overrides into
your common include file.
I'm curious why would you do that as opposed to just putting it in the
webroot with a single .htaccess file (or changing the global php.ini)?
I don't have access to the global ini file.  As for .htaccess - I 
thought your previous (?) said I would need to have multiple of them also.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/25/2013 10:56 AM, Jim Giner wrote:

On 1/24/2013 7:07 PM, Matt Pelmear wrote:

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script)
into all of my possible folders under my web root.  NOt exactly an
elegant solution, but with the script, easy to maintain.



Honestly, you would be better off just putting those 6 overrides into
your common include file.
I'm curious why would you do that as opposed to just putting it in the
webroot with a single .htaccess file (or changing the global php.ini)?

I don't have access to the global ini file.  As for .htaccess - I
thought your previous (?) said I would need to have multiple of them also.
Upon further research - I find that my domain has .htaccess files 
throughout the entire directory structure - not by my doing.  So I'm 
guessing that would mean having my individual php settings applied to each?


I think that I'll stay with a php.ini override file in each of my 
executable folders - now that I have the script necessary to easily 
apply new versions from my master one when needed.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] pdo ?

[Jim Giner]

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's view source to see that is 
exactly how it exists in the page.


When I hit a submit button and my script retrieves the 'post' vars my 
debugging steps are showing that the var $_POST['team'] contains the 
above value with a backslash (\) already inserted.  This is causing me a 
problem when I then try to use pdo-quote to safely encode it for 
updating my sql database.


My question is - why does the POST var show the \ char before I execute 
the 'quote' function?


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post

Every pro has this feature (magic_quotes_gpc) turned off. If you 
understand SQL Injection vulnerabilities, and properly bind things into 
your queries, I would recommend disabling it.


-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's view source to see that is 
exactly how it exists in the page.


When I hit a submit button and my script retrieves the 'post' vars my 
debugging steps are showing that the var $_POST['team'] contains the 
above value with a backslash (\) already inserted. This is causing me 
a problem when I then try to use pdo-quote to safely encode it for 
updating my sql database.


My question is - why does the POST var show the \ char before I 
execute the 'quote' function?





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 12:05 PM, Matt Pelmear wrote:

http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's view source to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo-quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't that carry on down to folders beneath it?


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 09:23 AM, Jim Giner wrote:

On 1/24/2013 12:05 PM, Matt Pelmear wrote:
http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:

ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's view source to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo-quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't that carry on down to folders beneath it?




I would check phpinfo() to see if it is being overridden.

-Matt


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Richard Quadling]

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:
 On 01/24/2013 09:23 AM, Jim Giner wrote:

 On 1/24/2013 12:05 PM, Matt Pelmear wrote:


 http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


 Every pro has this feature (magic_quotes_gpc) turned off. If you
 understand SQL Injection vulnerabilities, and properly bind things into
 your queries, I would recommend disabling it.

 -Matt

 On 01/24/2013 08:55 AM, Jim Giner wrote:

 ok - new to using pdo functions, but I thought I had a handle on it.

 I'm writing out to my page an input tag with the following value in it:

 49'ers

 I can confirm it by using my browser's view source to see that is
 exactly how it exists in the page.

 When I hit a submit button and my script retrieves the 'post' vars my
 debugging steps are showing that the var $_POST['team'] contains the
 above value with a backslash (\) already inserted. This is causing me
 a problem when I then try to use pdo-quote to safely encode it for
 updating my sql database.

 My question is - why does the POST var show the \ char before I
 execute the 'quote' function?


 You're right!  But I must not understand something.

 My root folder has a php.ini file with the magic quotes set off. Doesn't
 that carry on down to folders beneath it?


 I would check phpinfo() to see if it is being overridden.

 -Matt

Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!


-- 
Richard Quadling
Twitter : @RQuadling
EE : http://e-e.com/M_248814.html
Zend : http://bit.ly/9O8vFY

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value in it:

49'ers

I can confirm it by using my browser's view source to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' vars my
debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is causing me
a problem when I then try to use pdo-quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would be 
applied to all folders below my webroot, but apparently it is not happening.


How do 'pros' replicate their .ini settings thru all of the application 
folder?  Not thru settings within the scripts I hope - I thought I read 
that the was not a very efficient way to do it and that a php.ini file 
was the best since it would be merged with the master one installed by 
my hoster.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind things 
into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on it.

I'm writing out to my page an input tag with the following value 
in it:


49'ers

I can confirm it by using my browser's view source to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' 
vars my

debugging steps are showing that the var $_POST['team'] contains the
above value with a backslash (\) already inserted. This is 
causing me

a problem when I then try to use pdo-quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would be 
applied to all folders below my webroot, but apparently it is not 
happening.


How do 'pros' replicate their .ini settings thru all of the 
application folder?  Not thru settings within the scripts I hope - I 
thought I read that the was not a very efficient way to do it and that 
a php.ini file was the best since it would be merged with the master 
one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings for a 
particular host on a server. (Granted I never work in shared servers)
Assuming you are using Apache and the standard module configuration, you 
can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want to 
read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may 
need to consider other options (changing the setting globally or doing a 
require_once() of your config changes?, or see the FastCGI example here: 
http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt
It sounds like I'll have to modify multiple htaccess files - which is 
about the same as this php.ini problem - having multiples perhaps.


I've been working fine with a php.ini that merges onto the full ini 
file.  Suddenly this new appl is not seeing the overrides.


Any info you can point me to about how php.ini files work?  The manual 
doesn't get very specific about it.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Karl DeSaulniers]

On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post


Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind  
things into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle  
on it.


I'm writing out to my page an input tag with the following  
value in it:


49'ers

I can confirm it by using my browser's view source to see  
that is

exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'  
vars my
debugging steps are showing that the var $_POST['team']  
contains the
above value with a backslash (\) already inserted. This is  
causing me
a problem when I then try to use pdo-quote to safely encode  
it for

updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.  
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in  
my webroot folder. I was under the impression that the overrides  
would be applied to all folders below my webroot, but apparently it  
is not happening.


How do 'pros' replicate their .ini settings thru all of the  
application folder?  Not thru settings within the scripts I hope -  
I thought I read that the was not a very efficient way to do it and  
that a php.ini file was the best since it would be merged with the  
master one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings  
for a particular host on a server. (Granted I never work in shared  
servers)
Assuming you are using Apache and the standard module configuration,  
you can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want  
to read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you  
may need to consider other options (changing the setting globally or  
doing a require_once() of your config changes?, or see the FastCGI  
example here: http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on  
sanitizing yourself.


if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself  
as it will make you vulnerable.

But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 01:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post 




Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind 
things into

your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle 
on it.


I'm writing out to my page an input tag with the following 
value in it:


49'ers

I can confirm it by using my browser's view source to see 
that is

exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post' 
vars my
debugging steps are showing that the var $_POST['team'] 
contains the
above value with a backslash (\) already inserted. This is 
causing me
a problem when I then try to use pdo-quote to safely encode it 
for

updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off. 
Doesn't

that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in my 
webroot folder. I was under the impression that the overrides would 
be applied to all folders below my webroot, but apparently it is not 
happening.


How do 'pros' replicate their .ini settings thru all of the 
application folder?  Not thru settings within the scripts I hope - I 
thought I read that the was not a very efficient way to do it and 
that a php.ini file was the best since it would be merged with the 
master one installed by my hoster.




Jim,

Personally I rarely have the need to override the php.ini settings 
for a particular host on a server. (Granted I never work in shared 
servers)
Assuming you are using Apache and the standard module configuration, 
you can use .htaccess files to override many settings from php.ini


Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want 
to read through the comments here, too)

http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may 
need to consider other options (changing the setting globally or 
doing a require_once() of your config changes?, or see the FastCGI 
example here: 
http://www.askapache.com/php/php-htaccess-tips-tricks.html)


-Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on 
sanitizing yourself.


if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself 
as it will make you vulnerable.

But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




You shouldn't be sanitizing using addslashes() regardless, or using 
magic_quotes_gpc at all really, if you can help it.
See: http://php.net/manual/en/security.magicquotes.whynot.php 
(magic_quotes was deprecated because it is bad.)


-Matt


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 01:34 PM, Jim Giner wrote:


If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt
It sounds like I'll have to modify multiple htaccess files - which is 
about the same as this php.ini problem - having multiples perhaps.


I've been working fine with a php.ini that merges onto the full ini 
file.  Suddenly this new appl is not seeing the overrides.


Any info you can point me to about how php.ini files work?  The manual 
doesn't get very specific about it.




You would only have to modify multiple .htaccess files if you have 
multiple document roots that need this change applied to them.
Otherwise just create/edit the .htaccess file in the document root for 
the site you want the change applied to.
.htacess isn't always read by Apache. Some configurations tell it to 
read or not read that file, so you may need to check that as well.


phpinfo() will tell you which php.ini file is being used-- if you edit 
the one it says is being used you should see a change unless something 
else is overriding the setting (like an .htaccess file or your code).


-Matt


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 4:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post



Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind
things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle on
it.

I'm writing out to my page an input tag with the following value
in it:

49'ers

I can confirm it by using my browser's view source to see that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'
vars my
debugging steps are showing that the var $_POST['team'] contains
the
above value with a backslash (\) already inserted. This is
causing me
a problem when I then try to use pdo-quote to safely encode it for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.
Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in my
webroot folder. I was under the impression that the overrides would
be applied to all folders below my webroot, but apparently it is not
happening.

How do 'pros' replicate their .ini settings thru all of the
application folder?  Not thru settings within the scripts I hope - I
thought I read that the was not a very efficient way to do it and
that a php.ini file was the best since it would be merged with the
master one installed by my hoster.



Jim,

Personally I rarely have the need to override the php.ini settings for
a particular host on a server. (Granted I never work in shared servers)
Assuming you are using Apache and the standard module configuration,
you can use .htaccess files to override many settings from php.ini

Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want to
read through the comments here, too)
http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing
a require_once() of your config changes?, or see the FastCGI example
here: http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
 $var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on
sanitizing yourself.

if (get_magic_quotes_gpc()) {
 $var = stripslashes($var);
 //do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself
as it will make you vulnerable.
But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


Not the problem.  I have other overrides that I need to include somehow.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 6:03 PM, Matt Pelmear wrote:

On 01/24/2013 01:37 PM, Karl DeSaulniers wrote:


On Jan 24, 2013, at 2:15 PM, Matt Pelmear wrote:


On 01/24/2013 12:00 PM, Jim Giner wrote:

On 1/24/2013 1:41 PM, Richard Quadling wrote:

On 24 January 2013 17:48, Matt Pelmear mjpelm...@gmail.com wrote:

On 01/24/2013 09:23 AM, Jim Giner wrote:


On 1/24/2013 12:05 PM, Matt Pelmear wrote:



http://stackoverflow.com/questions/5801951/does-php-auto-escapes-quotes-in-string-which-is-passed-by-get-or-post



Every pro has this feature (magic_quotes_gpc) turned off. If you
understand SQL Injection vulnerabilities, and properly bind
things into
your queries, I would recommend disabling it.

-Matt

On 01/24/2013 08:55 AM, Jim Giner wrote:


ok - new to using pdo functions, but I thought I had a handle
on it.

I'm writing out to my page an input tag with the following
value in it:

49'ers

I can confirm it by using my browser's view source to see
that is
exactly how it exists in the page.

When I hit a submit button and my script retrieves the 'post'
vars my
debugging steps are showing that the var $_POST['team']
contains the
above value with a backslash (\) already inserted. This is
causing me
a problem when I then try to use pdo-quote to safely encode it
for
updating my sql database.

My question is - why does the POST var show the \ char before I
execute the 'quote' function?




You're right!  But I must not understand something.

My root folder has a php.ini file with the magic quotes set off.
Doesn't
that carry on down to folders beneath it?



I would check phpinfo() to see if it is being overridden.

-Matt


Create an info.php file containing ...

?php
phpinfo();

Save that in the directory containing PHP and one other directory.

Load them via your browser. See the settings for the magic_ and
see where the php.ini configuration file is being loaded.

It may be that your ini file is completely ignored!



Matt  Rich,

I have a small php.ini in my domain's  'php' folder as well as in my
webroot folder. I was under the impression that the overrides would
be applied to all folders below my webroot, but apparently it is not
happening.

How do 'pros' replicate their .ini settings thru all of the
application folder?  Not thru settings within the scripts I hope - I
thought I read that the was not a very efficient way to do it and
that a php.ini file was the best since it would be merged with the
master one installed by my hoster.



Jim,

Personally I rarely have the need to override the php.ini settings
for a particular host on a server. (Granted I never work in shared
servers)
Assuming you are using Apache and the standard module configuration,
you can use .htaccess files to override many settings from php.ini

Official reference pages:
http://php.net/manual/en/configuration.changes.php  (you might want
to read through the comments here, too)
http://httpd.apache.org/docs/current/howto/htaccess.html

Example and some discussion here as well:
http://davidwalsh.name/php-values-htaccess

If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or
doing a require_once() of your config changes?, or see the FastCGI
example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




You could just check for it with php and add or strip accordingly.

adding slashes if magic_quotes is disabled:

if (!get_magic_quotes_gpc()) {
$var = addslashes($var);
}

stripping slashes if magic_quotes is enabled and your planning on
sanitizing yourself.

if (get_magic_quotes_gpc()) {
$var = stripslashes($var);
//do your own sanitizing
}

I wouldn't suggest the last one if your not going to sanitize yourself
as it will make you vulnerable.
But all-in-all very simple to implement.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com




You shouldn't be sanitizing using addslashes() regardless, or using
magic_quotes_gpc at all really, if you can help it.
See: http://php.net/manual/en/security.magicquotes.whynot.php
(magic_quotes was deprecated because it is bad.)

-Matt


And I'm not.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Jim Giner]

On 1/24/2013 6:09 PM, Matt Pelmear wrote:

On 01/24/2013 01:34 PM, Jim Giner wrote:


If you are using another web server or running PHP as FastCGI you may
need to consider other options (changing the setting globally or doing a
require_once() of your config changes?, or see the FastCGI example here:
http://www.askapache.com/php/php-htaccess-tips-tricks.html)

-Matt

It sounds like I'll have to modify multiple htaccess files - which is
about the same as this php.ini problem - having multiples perhaps.

I've been working fine with a php.ini that merges onto the full ini
file.  Suddenly this new appl is not seeing the overrides.

Any info you can point me to about how php.ini files work?  The manual
doesn't get very specific about it.



You would only have to modify multiple .htaccess files if you have
multiple document roots that need this change applied to them.
Otherwise just create/edit the .htaccess file in the document root for
the site you want the change applied to.
.htacess isn't always read by Apache. Some configurations tell it to
read or not read that file, so you may need to check that as well.

phpinfo() will tell you which php.ini file is being used-- if you edit
the one it says is being used you should see a change unless something
else is overriding the setting (like an .htaccess file or your code).

-Matt

I took my 6 line override php.ini file and replicated (with a script) 
into all of my possible folders under my web root.  NOt exactly an 
elegant solution, but with the script, easy to maintain.


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo ?

[Matt Pelmear]

On 01/24/2013 04:02 PM, Jim Giner wrote:


I took my 6 line override php.ini file and replicated (with a script) 
into all of my possible folders under my web root.  NOt exactly an 
elegant solution, but with the script, easy to maintain.




Honestly, you would be better off just putting those 6 overrides into 
your common include file.
I'm curious why would you do that as opposed to just putting it in the 
webroot with a single .htaccess file (or changing the global php.ini)?


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO ?

[Jim Giner]

Doing some conversion - looking for a solution.

Currently I do something like this:

while (list($var1,$var2) = mysql_fetch_array($qrslts))
{
handle the vars
}

Is there something in the PDO functions that emulates this same ability? 
 Some of my uses of the sql syntax have many more vars and I'm
trying to avoid having to move the fetch-ed fields one at a time to vars 
that I wish to use.

I already tried:

list($var1,$var2) = $qrslts-fetch(PDO::FETCH_ASSOC);

this does not populate the vars.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO ?

[Kapu®]

On 15. 1. 2013 18:25, Jim Giner wrote:

Doing some conversion - looking for a solution.

Currently I do something like this:

while (list($var1,$var2) = mysql_fetch_array($qrslts))
{
handle the vars
}

Is there something in the PDO functions that emulates this same 
ability?  Some of my uses of the sql syntax have many more vars and I'm
trying to avoid having to move the fetch-ed fields one at a time to 
vars that I wish to use.

I already tried:

list($var1,$var2) = $qrslts-fetch(PDO::FETCH_ASSOC);

this does not populate the vars.


Hi,

using PDO::FETCH_NUM instead PDO::FETCH_ASSOC should do the job.

Kapu

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO user question

[Matt Pelmear]

Brandon: RAM is cheap, but not always expendable depending on what 
you're doing.
Using count() on results from PDOStatement::fetchAll() is fine on small 
result sets.
Keep in mind that this will certainly blow up on very large result sets, 
especially depending on your memory_limit for php. Avoiding this problem 
is the primary reason I use prepared statements for retrieving data.


If you're working with small result sets you could avoid using prepared 
statements altogether, which would greatly simplify doing something like 
getting a row count.


If you don't absolutely need your code to be portable across multiple 
database servers, I would encourage you to experiment with the 
rowCount() method and see if it works for your setup. (I ran a quick 
test in my mysql environment and it worked fine for a SELECT statement. 
Sqlite doesn't seem to support it because the actual sqlite library 
doesn't support it. Haven't looked into postgres or others.)


Another thing to be aware of (at least with mysql), and speaking to 
Lester's point, is that you can use SQL_CALC_FOUND_ROWS (with mysql, 
anyway) to get a total count when you are using LIMITs on your result 
set (which would be advisable if you're returning these results in a web 
interface).


-Matt

On 09/09/2012 02:40 PM, brandon wrote:
That's how I'd do it. Extend the PDO interface on your abstract class 
to include a num_rows() method that utilizes that higher level 
count($this-result). It might be a little more overhead... but RAM is 
cheap... and there's always forking/extending the library in C/C++...


-Brandon

On 2012-09-09 11:49, Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize
PDOStatement::FetchAll() and then do a count() on that result set.

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort stefan.wixf...@gmx.de 
wrote:



Hi Jim

I've had some success with querying using pdo and prepared 
statements as

well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by a
Select?  The documentation is very clear that PDO doesn't return that
value for a Select statement (depending upon driver?) and there were a
couple of solutions that made no sense to me.  There was even one that
did a completely separate query just to get the row count which makes
even less sense.


I believe you are referring to SELECT COUNT(*)...
Because I couldn't find a different way I use that.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php







--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO user question

[Stefan Wixfort]

Hi Jim


I've had some success with querying using pdo and prepared statements as
well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by a
Select?  The documentation is very clear that PDO doesn't return that
value for a Select statement (depending upon driver?) and there were a
couple of solutions that made no sense to me.  There was even one that
did a completely separate query just to get the row count which makes
even less sense.


I believe you are referring to SELECT COUNT(*)...
Because I couldn't find a different way I use that.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO user question

[Michael Stowe]

How are you using the number?  Probably the easiest way is to utilize 
PDOStatement::FetchAll() and then do a count() on that result set. 

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort stefan.wixf...@gmx.de wrote:

 Hi Jim
 
 I've had some success with querying using pdo and prepared statements as
 well.  One thing that I'm curious about is
 
 How does one handle the need to get the number of rows returned by a
 Select?  The documentation is very clear that PDO doesn't return that
 value for a Select statement (depending upon driver?) and there were a
 couple of solutions that made no sense to me.  There was even one that
 did a completely separate query just to get the row count which makes
 even less sense.
 
 I believe you are referring to SELECT COUNT(*)...
 Because I couldn't find a different way I use that.
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php
 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO user question

[brandon]

That's how I'd do it. Extend the PDO interface on your abstract class 
to include a num_rows() method that utilizes that higher level 
count($this-result). It might be a little more overhead... but RAM is 
cheap... and there's always forking/extending the library in C/C++...


-Brandon

On 2012-09-09 11:49, Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize
PDOStatement::FetchAll() and then do a count() on that result set.

- Mike



Sent from my iPhone

On Sep 9, 2012, at 11:42 AM, Stefan Wixfort stefan.wixf...@gmx.de 
wrote:



Hi Jim

I've had some success with querying using pdo and prepared 
statements as

well.  One thing that I'm curious about is

How does one handle the need to get the number of rows returned by 
a
Select?  The documentation is very clear that PDO doesn't return 
that
value for a Select statement (depending upon driver?) and there 
were a
couple of solutions that made no sense to me.  There was even one 
that
did a completely separate query just to get the row count which 
makes

even less sense.


I believe you are referring to SELECT COUNT(*)...
Because I couldn't find a different way I use that.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO user question

[Lester Caine]

Michael Stowe wrote:

How are you using the number?  Probably the easiest way is to utilize 
PDOStatement::FetchAll() and then do a count() on that result set.


There are two things to bear in mind here. Often you are only displaying a 
subset of records - ten per page perhaps - and so a count of the total helps to 
populate the navigation tools. COUNT(*) is not the most efficient way of doing 
that when dealing with large numbers of records, and so keeping a table with a 
set of counters that are populated via triggers on insert and delete help to 
speed up that process. FetchAll() should then always produce a full set of 
records except for the last page ...


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO user question

[Jim Giner]

I finally delved into learning how I was going to replace my MYSQL calls 
with a different interface.  Had to go with PDO since my hoster doesn't 
support MYSQLI for my plan.


I've had some success with querying using pdo and prepared statements as 
well.  One thing that I'm curious about is


How does one handle the need to get the number of rows returned by a 
Select?  The documentation is very clear that PDO doesn't return that 
value for a Select statement (depending upon driver?) and there were a 
couple of solutions that made no sense to me.  There was even one that 
did a completely separate query just to get the row count which makes 
even less sense.


Will PDO ever come up with an accurate row count function?
Does someone have a tried and true snippet they can share?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO Mysql data truncation missing error

[Matt Pelmear]

Hello,

I'm trying to detect data truncation on insert to MySQL using PDO.

As far as I can tell, this gets reported at least in some cases (ex: 
http://drupal.org/node/1528628), but I have been unable to see this myself.


The test table I'm using has a column that is VARCHAR(5):

mysql describe test;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(10) unsigned | YES  | | NULL|   |
| data  | varchar(5)   | YES  | | NULL|   |
+---+--+--+-+-+---+
2 rows in set (0.00 sec)


My test script inserts a ten character string into the 5 character column:

 PHP TEST CODE 

$pdo = new PDO(
'mysql:host=localhost;dbname=test',
'username',
'password'
);

$pdo-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$retval = $pdo-query( 'INSERT INTO `test` (data) VALUES (1234567890)' );

print_r( $retval );

 END TEST CODE 

This results in a new row in the `test` table, truncated after the 5th 
character as expected, but the truncation is not reported. (running in 
php 5.3.2 and 5.3.4)
Running the same query directly in the mysql command line shows a 
warning as expected.


Am I missing something simple here?

Thanks,
Matt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO Vs MySQLi Vs SQL Queries.

[Donahue Trevor]

Hi Vinay,
No, using native mysql_* statement isn't always faster. Advantages are
obvious, as mentioned in the article you shared (object mapping, security,
performance), there's always things like caching and other stuff you don't
want to reinvent the wheel for and on big projects tend to be really
useful. So imho it's better using things like pdo.
Another thing... try using ORM http://redbeanphp.com sliced my dev type in
4 :)

On Tue, Feb 28, 2012 at 6:22 AM, Vinay Kannan viny...@gmail.com wrote:

 Hey Guys,

 I came across this article.

 http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/

 Though I've been working with PHP for over 2 yrs, i never bothered to use
 the techniques mentioned in the articles like this.
 What i do for DB connections and queries..is just simple sql queries in
 php,
 $sql={sql query};
 $runsql=mysql_query($sql);

 What i do take care is to clean the user inputs before querying the DB.

 Is this a good way? Coz i've seen more experienced programmers using the
 techniques mentioned in the article, with PDOs, MySQLi etc...

 I always thought there is a chance that these might slow the querying
 process?

 Please advice.

 Any help is appreciated.

 Thanks,
 Vinay Kannan.

Re: [PHP-DB] PDO Vs MySQLi Vs SQL Queries.

[Vinay Kannan]

Hi Trevor,

Thank You for your suggestion, yeah i think caching is something i
definitely want to look at and something thats important, and there is a
big project coming up, its pretty much the biggest i've taken up so far,
theres also a mobile app for android which needs to be done, so caching
definitely is something i wanna look at.

I've also downloaded RedBean, will play around on the same, looks Cool ! :)

Thanks,
Vinay

On Tue, Feb 28, 2012 at 4:31 PM, Donahue Trevor donahue.tre...@gmail.comwrote:

 Hi Vinay,
 No, using native mysql_* statement isn't always faster. Advantages are
 obvious, as mentioned in the article you shared (object mapping, security,
 performance), there's always things like caching and other stuff you don't
 want to reinvent the wheel for and on big projects tend to be really
 useful. So imho it's better using things like pdo.
 Another thing... try using ORM http://redbeanphp.com sliced my dev type
 in 4 :)

 On Tue, Feb 28, 2012 at 6:22 AM, Vinay Kannan viny...@gmail.com wrote:

 Hey Guys,

 I came across this article.

 http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/

 Though I've been working with PHP for over 2 yrs, i never bothered to use
 the techniques mentioned in the articles like this.
 What i do for DB connections and queries..is just simple sql queries in
 php,
 $sql={sql query};
 $runsql=mysql_query($sql);

 What i do take care is to clean the user inputs before querying the DB.

 Is this a good way? Coz i've seen more experienced programmers using the
 techniques mentioned in the article, with PDOs, MySQLi etc...

 I always thought there is a chance that these might slow the querying
 process?

 Please advice.

 Any help is appreciated.

 Thanks,
 Vinay Kannan.

[PHP-DB] PDO Vs MySQLi Vs SQL Queries.

[Vinay Kannan]

Hey Guys,

I came across this article.

http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/

Though I've been working with PHP for over 2 yrs, i never bothered to use
the techniques mentioned in the articles like this.
What i do for DB connections and queries..is just simple sql queries in php,
$sql={sql query};
$runsql=mysql_query($sql);

What i do take care is to clean the user inputs before querying the DB.

Is this a good way? Coz i've seen more experienced programmers using the
techniques mentioned in the article, with PDOs, MySQLi etc...

I always thought there is a chance that these might slow the querying
process?

Please advice.

Any help is appreciated.

Thanks,
Vinay Kannan.

[PHP-DB] PDO + The Role of PHP + SQLite - Am I missing something?

[Lawrance Shepstone]

Hi List,

It seems that I'm a little confused (and thus frustrated) with PDO in
general, and especially when it comes to PECL php_sqlite3.

I understand that PDO is a data-access abstraction layer, and as one, is
really good. However, the way I see it, is that PDO should just be an added
extra, and not the stock-standard for data-access. It should simply wrap a
set of low-level libraries that are still exposed to developers should they
choose not to use PDO (for whatever reason). While this is true for most
databases, how long will it be until the current low-level libraries like
PECL php_mysql and PECL php_mysqli are no longer maintained, and we are
forced to use PDO?

This can be seen with the PECL php_sqlite extension, which is not maintained
anymore. I understand why it's not maintained anymore, because there is PECL
php_sqlite3. The problem here is that PECL php_sqlite3 only offers an OOP
approach, and no procedural approach. So, for developers that want to
develop their own data abstraction layers (access or model), they have to
now wrap the PECL  php_sqlite3 SQLite3 set of classes. This makes the entire
layer slower, more complex, and therefore more error-prone. Most developers
are forced to use PDO for SQLite 3 access (due to PHP versions or other
reasons), which is still a set of classes that bloat any further abstraction
they may choose to develop.

The following posting shows the unnecessary confusion on this topic:

http://lists.macosforge.org/pipermail/macports-users/2009-January/013658.htm
l

Confusion like this has led to the development of an SQLite 3 procedural
extension:

http://php-sqlite3.sourceforge.net/pmwiki/pmwiki.php - not to be confused
with the current PECL php_sqlite3 (dash vs underscore)

But surely if the PECL php_sqlite3 extension has already been developed and
included in the default distributions of PHP, it must be possible to include
a procedural interface as well? How long will it be before we are forced to
use bloated objects for everything we need to do in PHP? Isn't that the
great thing about PHP? That at its core it's a great, fast scripting engine
with a few core libraries, and then everything else is implemented through
extensions?

I fully understand the power that OOP offers, and I understand that is a
vital part to the maturity of PHP as a *language*, but I just can't see why
the speed and elegance of interfacing with low-level extensions using
procedural methods should be completely dropped. For me, as a developer, I
have enjoyed the freedom (of choice and ability) that PHP offers - the
ability to create the next best data-abstraction layer, or MVC framework.
Why should these important innovations be made more difficult by forcing the
use of bloated libraries - simply because they adhere to strict OOP best
practices, and ultimately force developers to create their own extensions,
when it takes less time and work to expose the low-level API like the one
already wrapped by PECL php_sqlite3.

Wouldn't it provide developers with more opportunity if the current PECL
php_sqlite3 extension was dropped in favour of PDO, and replaced with a more
low-level library like PECL php_sqlite for version 3 databases? That way
developers would be able to develop their own access and model abstraction
layers with good performance should they choose to, or make use of PDO
should they need something a little more robust and feature-full? The Zend
Framework's Zend_DB* objects provide both data-access-abstraction and
data-model-abstraction, but still have to wrap PDO for data-access.

The problem with developing another custom extension is that hosting
providers only use stable releases of PHP and PECL, which makes it near
impossible to have a custom extension enabled in a shared hosting
environment. Not to mention, again, that the notion of developing a custom
extension to expose functionality that already exists (like that already
wrapped by PECL php-sqlite3), is insane.

Am I missing something?

Where do I get involved to help improve the situation?

All the best,
Lawrance.

Any intelligent fool can make things bigger and more complex...
It takes a touch of genius - and a lot of courage to move in the
opposite direction. - Albert Einstein


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO + The Role of PHP + SQLite - Am I missing something?

[Niel Archer]

 Hi List,
 
 It seems that I'm a little confused (and thus frustrated) with PDO in
 general, and especially when it comes to PECL php_sqlite3.
 
 I understand that PDO is a data-access abstraction layer, and as one, is
 really good. However, the way I see it, is that PDO should just be an added
 extra, and not the stock-standard for data-access. It should simply wrap a
 set of low-level libraries that are still exposed to developers should they
 choose not to use PDO (for whatever reason). While this is true for most
 databases, how long will it be until the current low-level libraries like
 PECL php_mysql and PECL php_mysqli are no longer maintained, and we are
 forced to use PDO?
 
 This can be seen with the PECL php_sqlite extension, which is not maintained
 anymore. I understand why it's not maintained anymore, because there is PECL
 php_sqlite3. The problem here is that PECL php_sqlite3 only offers an OOP
 approach, and no procedural approach. So, for developers that want to
 develop their own data abstraction layers (access or model), they have to
 now wrap the PECL  php_sqlite3 SQLite3 set of classes. This makes the entire
 layer slower, more complex, and therefore more error-prone. Most developers
 are forced to use PDO for SQLite 3 access (due to PHP versions or other
 reasons), which is still a set of classes that bloat any further abstraction
 they may choose to develop.
 
 The following posting shows the unnecessary confusion on this topic:
 
 http://lists.macosforge.org/pipermail/macports-users/2009-January/013658.htm
 l

Most of the confusion there is based on misunderstandings, I would say.

 Confusion like this has led to the development of an SQLite 3 procedural
 extension:
 
 http://php-sqlite3.sourceforge.net/pmwiki/pmwiki.php - not to be confused
 with the current PECL php_sqlite3 (dash vs underscore)
 
 But surely if the PECL php_sqlite3 extension has already been developed and
 included in the default distributions of PHP, it must be possible to include
 a procedural interface as well? How long will it be before we are forced to
 use bloated objects for everything we need to do in PHP?

When no procedural developers step up and write the extensions. No point
complaining that some developers with an interest in an OOP
implementation of an OOP library took the time to do the work.

Isn't that the
 great thing about PHP? That at its core it's a great, fast scripting engine
 with a few core libraries, and then everything else is implemented through
 extensions?

 I fully understand the power that OOP offers, and I understand that is a
 vital part to the maturity of PHP as a *language*, but I just can't see why
 the speed and elegance of interfacing with low-level extensions using
 procedural methods should be completely dropped. For me, as a developer, I
 have enjoyed the freedom (of choice and ability) that PHP offers - the
 ability to create the next best data-abstraction layer, or MVC framework.
 Why should these important innovations be made more difficult by forcing the
 use of bloated libraries

Something of an exaggeration. As the sqlite3 library has an OOP API, it's
more likely a procedural interface would be adding the bloat, if any.

- simply because they adhere to strict OOP best
 practices, and ultimately force developers to create their own extensions,
 when it takes less time and work to expose the low-level API like the one
 already wrapped by PECL php_sqlite3.

Not true, as noted above, SQLite3 uses an OOP API at low-level

 Wouldn't it provide developers with more opportunity if the current PECL
 php_sqlite3 extension was dropped in favour of PDO, and replaced with a more
 low-level library like PECL php_sqlite for version 3 databases? That way
 developers would be able to develop their own access and model abstraction
 layers with good performance should they choose to, or make use of PDO
 should they need something a little more robust and feature-full? The Zend
 Framework's Zend_DB* objects provide both data-access-abstraction and
 data-model-abstraction, but still have to wrap PDO for data-access.

The PECL extension is NOT the current implementation of the extension.
The PDO/PDO_Sqlite extension/driver are separate to the Sqlite3
extension.
I'm not familiar with ZF's way of doing things, but I doubt is *has* to
use PDO. This is more likely a choice they made.

 The problem with developing another custom extension is that hosting
 providers only use stable releases of PHP and PECL, which makes it near
 impossible to have a custom extension enabled in a shared hosting
 environment. Not to mention, again, that the notion of developing a custom
 extension to expose functionality that already exists (like that already
 wrapped by PECL php-sqlite3), is insane.

 Agreed. So take your pick of PDO/PDO_sqlite or just SQLite3 extensions.
Both are enabled in PHP 5.3.0 by default, and should be in 5.2 builds
also.

 Am I missing something?

PDO is not needed to access SQLite3. 

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Richard Quadling]

On 24 March 2010 18:04, Aaron Paetznick aar...@critd.com wrote:
 Thanks for the advise.  I wanted a more automatic method of prefixing column
 names, but I ended up just aliasing them all.

 I know I could always just issue multiple SELECTs, but I wish MySQL would
 support this sort of functionality natively.  I'd really like to reference
 my results as $result[table0][column0] from a single SELECT.

 Anyways, thanks all.


 --Aaron


 On 3/24/2010 11:28 AM, Niel Archer wrote:

 Many of my MySQL tables have columns with the same name.  I want to have
 PDO include table names in named result sets.  For example:

    $sth = $dbh-prepare(SELECT * FROM table0, table1);
    $result = $sth-fetchAll(PDO::FETCH_ASSOC);


 I want $result to be organized like:

    echo $result[table0.column0];
    echo $result[table0.column1];
    echo $result[table1.column0];
    echo $result[table1.column1];


 Or, alternatively:

    echo $result[table0][column0];
    echo $result[table0][column1];
    echo $result[table1][column0];
    echo $result[table1][column1];


 Any ideas?  Thanks!

 Sounds like you want to UNION two SELECTs
 http://dev.mysql.com/doc/refman/5.0/en/union.html

 (SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
 UNION
 (SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)


 --Aaron


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php

 --
 Niel Archer
 niel.archer (at) blueyonder.co.uk




 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



I use MS SQL and have used other DBs. I've never seen the table name
as part of the column name in a result set on any of them.


-- 
-
Richard Quadling
Standing on the shoulders of some very clever giants!
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Aaron Paetznick]

Many of my MySQL tables have columns with the same name.  I want to have 
PDO include table names in named result sets.  For example:


  $sth = $dbh-prepare(SELECT * FROM table0, table1);
  $result = $sth-fetchAll(PDO::FETCH_ASSOC);


I want $result to be organized like:

  echo $result[table0.column0];
  echo $result[table0.column1];
  echo $result[table1.column0];
  echo $result[table1.column1];


Or, alternatively:

  echo $result[table0][column0];
  echo $result[table0][column1];
  echo $result[table1][column0];
  echo $result[table1][column1];


Any ideas?  Thanks!


--Aaron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Niel Archer]

 Many of my MySQL tables have columns with the same name.  I want to have 
 PDO include table names in named result sets.  For example:
 
$sth = $dbh-prepare(SELECT * FROM table0, table1);
$result = $sth-fetchAll(PDO::FETCH_ASSOC);
 
 
 I want $result to be organized like:
 
echo $result[table0.column0];
echo $result[table0.column1];
echo $result[table1.column0];
echo $result[table1.column1];
 
 
 Or, alternatively:
 
echo $result[table0][column0];
echo $result[table0][column1];
echo $result[table1][column0];
echo $result[table1][column1];
 
 
 Any ideas?  Thanks!

Sounds like you want to UNION two SELECTs
http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
UNION
(SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)


 --Aaron
 
 
 -- 
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php

--
Niel Archer
niel.archer (at) blueyonder.co.uk



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Richard Quadling]

On 24 March 2010 16:05, Aaron Paetznick aar...@critd.com wrote:
 Many of my MySQL tables have columns with the same name.  I want to have PDO
 include table names in named result sets.  For example:

  $sth = $dbh-prepare(SELECT * FROM table0, table1);
  $result = $sth-fetchAll(PDO::FETCH_ASSOC);


 I want $result to be organized like:

  echo $result[table0.column0];
  echo $result[table0.column1];
  echo $result[table1.column0];
  echo $result[table1.column1];


 Or, alternatively:

  echo $result[table0][column0];
  echo $result[table0][column1];
  echo $result[table1][column0];
  echo $result[table1][column1];


 Any ideas?  Thanks!


 --Aaron


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php



The data coming from the server dictates the column names, not PDO.

If you need to identify the names, then use aliases in the SQL.





-- 
-
Richard Quadling
Standing on the shoulders of some very clever giants!
EE : http://www.experts-exchange.com/M_248814.html
EE4Free : http://www.experts-exchange.com/becomeAnExpert.jsp
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO include table name prefixes in FETCH_ASSOC

[Aaron Paetznick]

Thanks for the advise.  I wanted a more automatic method of prefixing 
column names, but I ended up just aliasing them all.


I know I could always just issue multiple SELECTs, but I wish MySQL 
would support this sort of functionality natively.  I'd really like to 
reference my results as $result[table0][column0] from a single SELECT.


Anyways, thanks all.


--Aaron


On 3/24/2010 11:28 AM, Niel Archer wrote:

Many of my MySQL tables have columns with the same name.  I want to have
PDO include table names in named result sets.  For example:

$sth = $dbh-prepare(SELECT * FROM table0, table1);
$result = $sth-fetchAll(PDO::FETCH_ASSOC);


I want $result to be organized like:

echo $result[table0.column0];
echo $result[table0.column1];
echo $result[table1.column0];
echo $result[table1.column1];


Or, alternatively:

echo $result[table0][column0];
echo $result[table0][column1];
echo $result[table1][column0];
echo $result[table1][column1];


Any ideas?  Thanks!


Sounds like you want to UNION two SELECTs
http://dev.mysql.com/doc/refman/5.0/en/union.html

(SELECT col1, col2, col4 FROM table1 WHERE ... ORDER BY ...)
UNION
(SELECT col1, col2, col4 FROM table2 WHERE ... ORDER BY ...)



--Aaron


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


--
Niel Archer
niel.archer (at) blueyonder.co.uk





--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Lester Caine]

Samuel ROZE wrote:
Hi, 
Thanks for your reply.


In fact, my request returns a result which i get with the fetch method.
But, it must returns other informations, which are not in the result...
This informations are neturned before the result in console...

So, i don't know how I can get that.


PDO does not support many of the 'additional' features that databases 
provide. It's designed to provide a 'lowest common denominator' solution 
JUST for the data. If you are NOT planning to use any other database 
then you may well find that the native postgres driver will provide 
these additional facilities.


I use Firebird myself so can't comment on the 'fine detail' on postgres, 
but some of the Firebird options can't easily be supported in PDO ;)


--
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Samuel ROZE]

Thanks a lot ! I'm now understanding why PDO exists.. It is not for
replace the actual PostgreSQL/MySQL/SQLite/Firebird/... PHP drivers but
for help to developp with many DB with one code.

Well... I can developp an extension to PDO PostgreSQL driver, is that
correct ? It may help many users like me... :-)

Thanks Lester !
Samuel.

Le lundi 05 octobre 2009 à 08:36 +0100, Lester Caine a écrit :
 Samuel ROZE wrote:
  Hi, 
  Thanks for your reply.
  
  In fact, my request returns a result which i get with the fetch method.
  But, it must returns other informations, which are not in the result...
  This informations are neturned before the result in console...
  
  So, i don't know how I can get that.
 
 PDO does not support many of the 'additional' features that databases 
 provide. It's designed to provide a 'lowest common denominator' solution 
 JUST for the data. If you are NOT planning to use any other database 
 then you may well find that the native postgres driver will provide 
 these additional facilities.
 
 I use Firebird myself so can't comment on the 'fine detail' on postgres, 
 but some of the Firebird options can't easily be supported in PDO ;)
 
 -- 
 Lester Caine - G8HFL
 -
 Contact - http://lsces.co.uk/wiki/?page=contact
 L.S.Caine Electronic Services - http://lsces.co.uk
 EnquirySolve - http://enquirysolve.com/
 Model Engineers Digital Workshop - http://medw.co.uk//
 Firebird - http://www.firebirdsql.org/index.php
 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO PgSQL: _pdo_pgsql_notice

[Samuel ROZE]

Hi !

I'm reading the source of PDO PostgreSQL driver and i see that there's
an empty function, _pdo_pgsql_notice, which is very interesting ! I
think that it is used when PostgreSQL throw Notices.
But, nothing is done...

I want to know if somebody know why the content of the function is
commented:

static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char *message) /*
{{{ */
{
/*  pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh-driver_data; */
}

(line 98 of /etc/pdo_pgsql/pgsql_driver.c - PHP 5.2 - r272374)

How can I store the content of message into a field of the
PDOStatement ? I view that there's only a pdo_dbh_t param, I may just
store this message into a field of the PDO class ?

Thanks in advance.
Samuel.


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO PgSQL: _pdo_pgsql_notice

[Samuel ROZE]

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into errmsg (which I can get with PDO::errorInfo) the
notice.

The _pdo_pgsql_notice function is called everytime that there's an
notice. The notice message is in the message var. This what i had
tried:

---
static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char *message) /*
{{{ */
{
pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh-driver_data;
pdo_pgsql_error_info *einfo = H-einfo;

einfo-errcode = 1;

if (einfo-errmsg) {
pefree(einfo-errmsg, dbh-is_persistent);
einfo-errmsg = NULL;
}
einfo-errmsg = _pdo_pgsql_trim_message(message, dbh-is_persistent);
}
---

And in pdo_pgsql_fetch_error_func, i've added an else condition to
view if errmsg is null are not...

---
if (einfo-errcode) {
add_next_index_long(info, einfo-errcode);
add_next_index_string(info, einfo-errmsg, 1);
} else {
zend_throw_exception_ex(php_pdo_get_exception(), 0 TSRMLS_CC,
einfo-errcode is false : %s , %s, einfo-errcode, einfo-errmsg);
}
---

And, I've a function which send some notices (when I throw exception
from _pdo_pgsql_notice with the message var, I see the notice ! :-)
But, einfo-errcode is null !

- Fatal error: Uncaught exception 'PDOException' with message
'einfo-errcode is false : (null) , (null)'
in /etc/php-5.2.10/test/pgsql_notice.php:22
Stack trace:
#0 /etc/php-5.2.10/test/pgsql_notice.php(22): PDO-errorInfo()
#1 {main}
  thrown in /etc/php-5.2.10/test/pgsql_notice.php on line 22

Is anyone can help me ?
Thanks a lot !

Samuel. (French)

Le lundi 05 octobre 2009 à 20:58 +0200, Samuel ROZE a écrit :
 Hi !
 
 I'm reading the source of PDO PostgreSQL driver and i see that there's
 an empty function, _pdo_pgsql_notice, which is very interesting ! I
 think that it is used when PostgreSQL throw Notices.
 But, nothing is done...
 
 I want to know if somebody know why the content of the function is
 commented:
 
 static void _pdo_pgsql_notice(pdo_dbh_t *dbh, const char *message) /*
 {{{ */
 {
 /*pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh-driver_data; */
 }
 
 (line 98 of /etc/pdo_pgsql/pgsql_driver.c - PHP 5.2 - r272374)
 
 How can I store the content of message into a field of the
 PDOStatement ? I view that there's only a pdo_dbh_t param, I may just
 store this message into a field of the PDO class ?
 
 Thanks in advance.
 Samuel.
 
 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO PgSQL: _pdo_pgsql_notice

[Chris]

Samuel ROZE wrote:

To use PostgreSQL's Notices with PDO, i'm modifying the source for
trying to store into errmsg (which I can get with PDO::errorInfo) the
notice.

The _pdo_pgsql_notice function is called everytime that there's an
notice. The notice message is in the message var. This what i had
tried:


You'll need to take this suggestion to the php-internals mailing list. 
They write and discuss the C code behind php so they'll be able to help you.


--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Samuel ROZE]

Hi !

I'm new on this mailling list, so i don't realy know if you know the
response and if it is realy here that I have to ask my question :-)

I'm working with PostgreSQL (8.3 form sources) and PDO (PHP 5.2.10 from
sources). In a Postgres function, I have a RAISE NOTICE command. My
function works like that in console:

= SELECT * FROM public.test_info();
NOTICE: An information...

 test_info

 ok
(1 line)

There's a NOTICE, like I want ! :-) But, when i'm using PDO, I don't
know how I can get this NOTICE, which will provide me some informations
about the usage of the function.

Code:
--
?
$sql = new PDO('...');

$stat = $sql-query('SELECT * FROM public.test_info()');
var_dump($stat);
?
--

Output:
--
object(PDOStatement)#4 (1) {
  [queryString]=
  string(34) SELECT * FROM public.test_info()
}
--

Is there someone who know how I can get that ? I read some thinks about
that and pg_last_notice may returns the NOTICE, but with PDO ?

Thanks !
Samuel (French).


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Kesavan Rengarajan]

Hi,
Query returns an iterable object (thanks to the comments in the php  
site) and that's why when you do a dump you just see the String,
If you just want to get one row from the resultset you can get it like  
this:

$stat = $sql-query('SELECT * FROM public.test_info()')-fetch();

More information on the PDO::query method can be found here: 
http://au.php.net/manual/en/pdo.query.php

Cheers
Sent from my iPhone

On 05/10/2009, at 2:06 AM, Samuel ROZE samuel.r...@aliceadsl.fr wrote:


Hi !

I'm new on this mailling list, so i don't realy know if you know the
response and if it is realy here that I have to ask my question :-)

I'm working with PostgreSQL (8.3 form sources) and PDO (PHP 5.2.10  
from

sources). In a Postgres function, I have a RAISE NOTICE command. My
function works like that in console:

= SELECT * FROM public.test_info();
NOTICE: An information...

test_info

ok
(1 line)

There's a NOTICE, like I want ! :-) But, when i'm using PDO, I don't
know how I can get this NOTICE, which will provide me some  
informations

about the usage of the function.

Code:
--
?
$sql = new PDO('...');

$stat = $sql-query('SELECT * FROM public.test_info()');
var_dump($stat);
?
--

Output:
--
object(PDOStatement)#4 (1) {
 [queryString]=
 string(34) SELECT * FROM public.test_info()
}
--

Is there someone who know how I can get that ? I read some thinks  
about

that and pg_last_notice may returns the NOTICE, but with PDO ?

Thanks !
Samuel (French).


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and PostgreSQL - RAISE NOTICE

[Samuel ROZE]

Hi, 
Thanks for your reply.

In fact, my request returns a result which i get with the fetch method.
But, it must returns other informations, which are not in the result...
This informations are neturned before the result in console...

So, i don't know how I can get that.

Samuel.

Le lundi 05 octobre 2009 à 09:55 +1100, Kesavan Rengarajan a écrit :
 Hi,
 Query returns an iterable object (thanks to the comments in the php  
 site) and that's why when you do a dump you just see the String,
 If you just want to get one row from the resultset you can get it like  
 this:
 $stat = $sql-query('SELECT * FROM public.test_info()')-fetch();
 
 More information on the PDO::query method can be found here: 
 http://au.php.net/manual/en/pdo.query.php
 
 Cheers
 Sent from my iPhone
 
 On 05/10/2009, at 2:06 AM, Samuel ROZE samuel.r...@aliceadsl.fr wrote:
 
  Hi !
 
  I'm new on this mailling list, so i don't realy know if you know the
  response and if it is realy here that I have to ask my question :-)
 
  I'm working with PostgreSQL (8.3 form sources) and PDO (PHP 5.2.10  
  from
  sources). In a Postgres function, I have a RAISE NOTICE command. My
  function works like that in console:
 
  = SELECT * FROM public.test_info();
  NOTICE: An information...
 
  test_info
  
  ok
  (1 line)
 
  There's a NOTICE, like I want ! :-) But, when i'm using PDO, I don't
  know how I can get this NOTICE, which will provide me some  
  informations
  about the usage of the function.
 
  Code:
  --
  ?
  $sql = new PDO('...');
 
  $stat = $sql-query('SELECT * FROM public.test_info()');
  var_dump($stat);
  ?
  --
 
  Output:
  --
  object(PDOStatement)#4 (1) {
   [queryString]=
   string(34) SELECT * FROM public.test_info()
  }
  --
 
  Is there someone who know how I can get that ? I read some thinks  
  about
  that and pg_last_notice may returns the NOTICE, but with PDO ?
 
  Thanks !
  Samuel (French).
 
 
  -- 
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php
 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO - SP Return Values supported?

[Chris Schroll]

Hey Guys,

I'm working with a SQL Server stored procedure that returns error codes; 
here is a very simple snippet of the SP.

DECLARE @ret int
BEGIN
SET @ret = 1
RETURN @ret
END

I can't figure out how to access the return value in PDO; I'd prefer not to 
use an OUT parameter, as alot of these Stored Procedures have already been 
written.  Here is an example of how I am currently calling the procedure in 
PHP.

$stmt = $this-db-prepare(EXECUTE usp_myproc ?, ?);
$stmt-bindParam(1, 'mystr', PDO::PARAM_STR);
$stmt-bindParam(2, 'mystr2', PDO::PARAM_STR);
$rs = $stmt-execute();
$result = $stmt-fetchAll(PDO::FETCH_ASSOC);

Is there a way to do this without an OUT parameter? 



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO buffered query problem

[Stewart Duncan]

Hi there,

I'm having some serious problems with the PHP Data Object functions. I'm 
trying to loop through a sizeable result set (~60k rows, ~1gig) using a 
buffered query to avoid fetching the whole set.


No matter what I do, the script just hangs on the PDO::query() - it 
seems the query is running unbuffered (why else would the change in 
result set size 'fix' the issue?). Here is my code to reproduce the problem:


?php
$Database = new PDO(
'mysql:host=localhost;port=3306;dbname=mydatabase',
'root',
'',
array(
PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true
)
);

$rQuery = $Database-query('SELECT id FROM mytable');

// This is never reached because the result set is too large
echo 'Made it through.';

foreach($rQuery as $aRow) {
print_r($aRow);
}
?

If I limit the query with some reasonable number, it works fine:

$rQuery = $Database-query('SELECT id FROM mytable LIMIT 10');

I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using the 
PDO::prepare() and PDO::execute() as well (though there are no 
parameters in the above query), both to no avail.


Any help would be appreciated,

Stewart

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO bindValue ORDER BY

[Chris]

Matthew Peltzer wrote:

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = SELECT * FROM `table` ORDER BY `$sort`;

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


Validate your data.

You're just making a bad query here.

I somehow get orderby to be field_that_does_not_exist.

You end up with:

select * from table where blah order by `field_that_does_not_exist`;

which will generate a mysql_error and give no results at all.

--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO bindValue ORDER BY

[Chris]

Chris wrote:

Matthew Peltzer wrote:

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = SELECT * FROM `table` ORDER BY `$sort`;

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


Validate your data.

You're just making a bad query here.

I somehow get orderby to be field_that_does_not_exist.

You end up with:

select * from table where blah order by `field_that_does_not_exist`;

which will generate a mysql_error and give no results at all.


Also mysql_real_escape_string needs a connection to do the escaping 
properly, so now you're doubling up.


1 connection for pdo
1 connection for mysql_real_escape_string to use

You could get away with mysql_escape_string however it doesn't take 
charset's into account so you may have some issues with those.


--
Postgresql  php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO bindValue ORDER BY

[Matthew Peltzer]

ok... this makes more sense now.  I know in the past I tried to do
something similar with table names in the WHERE clause, and that
didn't work in the same manner.

Is there a better way to do what I'm trying to do? that is, sorting
within the SQL statement based on a supplied column name without out
writing multiple SQL statements?

for now my work around is to some thing like:

$sort = mysql_real_escape_string($sort);
$sql = SELECT * FROM `table` ORDER BY `$sort`;

which makes me queasy because I spent a lot of time getting away from
inlining variables in SQL statements.


On Fri, Oct 31, 2008 at 6:46 PM, Post TUDBC [EMAIL PROTECTED] wrote:
 Technically, bound parameter is expecting a value, such as
WHERE ID=:id
 However, ORDER BY is followed by a field name, such as
   ORRDER BY ID
 So I don't think it should work.

 If it does work, then it is a sign that the database driver is not
 really preparing the statement (as it should for performance reason),
 but it is just substituiting values to compose a SQL (just for your
 convenience).

 On 10/31/08, Matthew Peltzer [EMAIL PROTECTED] wrote:
 Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?

  I find that in php 5.2.6 this works as expected:

  ?php
  $sql = 'SELECT * FROM `table` ORDER BY :sort';
  $stmt = $pdo-prepare($sql);
  $stmt-bindValue(':sort', $sort, PDO::PARAM_STR);
  $stmt-execute();
  print_r($stmt-fetchAll(PDO::FETCH_ASSOC));
  ?

  but under php5.2.5 the ORDER BY clause silently fails.  Also,
  parameters bound to SELECT or WHERE or LIMIT clauses function
  correctly, but ORDE BY still has no effect.  If I remove the
  $stmt-bindValue(':sort', $sort, PDO::PARAM_STR); line or the ORDER
  BY :sort I get a number of bound variables does not match number of
  tokens error.

  So it appears the parsing mechanism is funcitoning, but what ever is
  responsible for binding to ORDER BY is not.

  I've looked in bug reports and the change logs, but did not find a
  explicit reference to this issue.


  --
  -- Matthew Peltzer
  -- [EMAIL PROTECTED]


  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php






-- 
-- Matthew Peltzer
-- [EMAIL PROTECTED]

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO bindValue ORDER BY

[Post TUDBC]

Your workaround is probably what I would do myself.

Note: mysql_real_escape_string() is technically expecting a string
value, although there is no harm using it, and it's not a bad idea to
avoid possible SQL malicious codes. Alternately, you can also write a
simple function using regular expression to make sure that $sort is
one of your possible sort fields and nothing else.

http://www.tudbc.org


On 11/1/08, Matthew Peltzer [EMAIL PROTECTED] wrote:
 ok... this makes more sense now.  I know in the past I tried to do
  something similar with table names in the WHERE clause, and that
  didn't work in the same manner.

  Is there a better way to do what I'm trying to do? that is, sorting
  within the SQL statement based on a supplied column name without out
  writing multiple SQL statements?

  for now my work around is to some thing like:

  $sort = mysql_real_escape_string($sort);
  $sql = SELECT * FROM `table` ORDER BY `$sort`;

  which makes me queasy because I spent a lot of time getting away from
  inlining variables in SQL statements.



  On Fri, Oct 31, 2008 at 6:46 PM, Post TUDBC [EMAIL PROTECTED] wrote:
   Technically, bound parameter is expecting a value, such as
  WHERE ID=:id
   However, ORDER BY is followed by a field name, such as
 ORRDER BY ID
   So I don't think it should work.
  
   If it does work, then it is a sign that the database driver is not
   really preparing the statement (as it should for performance reason),
   but it is just substituiting values to compose a SQL (just for your
   convenience).
  
   On 10/31/08, Matthew Peltzer [EMAIL PROTECTED] wrote:
   Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?
  
I find that in php 5.2.6 this works as expected:
  
?php
$sql = 'SELECT * FROM `table` ORDER BY :sort';
$stmt = $pdo-prepare($sql);
$stmt-bindValue(':sort', $sort, PDO::PARAM_STR);
$stmt-execute();
print_r($stmt-fetchAll(PDO::FETCH_ASSOC));
?
  
but under php5.2.5 the ORDER BY clause silently fails.  Also,
parameters bound to SELECT or WHERE or LIMIT clauses function
correctly, but ORDE BY still has no effect.  If I remove the
$stmt-bindValue(':sort', $sort, PDO::PARAM_STR); line or the ORDER
BY :sort I get a number of bound variables does not match number of
tokens error.
  
So it appears the parsing mechanism is funcitoning, but what ever is
responsible for binding to ORDER BY is not.
  
I've looked in bug reports and the change logs, but did not find a
explicit reference to this issue.
  
  
--
-- Matthew Peltzer
-- [EMAIL PROTECTED]
  
  
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
  
  
  




 --

 -- Matthew Peltzer
  -- [EMAIL PROTECTED]

  --
  PHP Database Mailing List (http://www.php.net/)
  To unsubscribe, visit: http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO bindValue ORDER BY

[Matthew Peltzer]

Are pdo bound parameters within an ORDER BY clause broken in php 5.2.5?

I find that in php 5.2.6 this works as expected:

?php
$sql = 'SELECT * FROM `table` ORDER BY :sort';
$stmt = $pdo-prepare($sql);
$stmt-bindValue(':sort', $sort, PDO::PARAM_STR);
$stmt-execute();
print_r($stmt-fetchAll(PDO::FETCH_ASSOC));
?

but under php5.2.5 the ORDER BY clause silently fails.  Also,
parameters bound to SELECT or WHERE or LIMIT clauses function
correctly, but ORDE BY still has no effect.  If I remove the
$stmt-bindValue(':sort', $sort, PDO::PARAM_STR); line or the ORDER
BY :sort I get a number of bound variables does not match number of
tokens error.

So it appears the parsing mechanism is funcitoning, but what ever is
responsible for binding to ORDER BY is not.

I've looked in bug reports and the change logs, but did not find a
explicit reference to this issue.


-- 
-- Matthew Peltzer
-- [EMAIL PROTECTED]

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[Thodoris]

O/H TK ??:

At 03:21 AM 7/9/2008, Thodoris wrote:
  

At 04:16 PM 7/8/2008, Philip Thompson wrote:


On Jul 8, 2008, at 11:55 AM, TK wrote:

I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
$contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an Array to string conversion notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)
 
  

$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = ' . implode (',', $contents) . ';

Hope that helps,


Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).
 
  

Perhaps the implode suggested above is the solution for the prepared statement. 
I think that the problem is that you cannot bind a value with an array.
So you have  to implode the array into a string before binding it to a value. 
The other goes as you suggested:

$stmt = $pdo-prepare('
select *


from mytable
  

where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Where contents is:

$pre_contents = array('mystring1', 'mystring2');

$contents = implode(',',$pre_contents);

or 
$contents = 'mystring';




As per my original question, that does not work:

  
If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';



Binding a comma-separated list (i.e. what implode creates) does not do what is wanted 
here - it appears instead to treat the whole list as one entry, i.e. it's 
like doing this:
   where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
   where myfield IN ('mystring1','mystring2')

Hence, my original question!  Is there a way to accomplish this with PDO and 
prepared statements?

- TK


  

Why don't you work this around. Since you may do anything with strings in php 
using the (.) operator try this:

?php

$a = array('string1','string2');

$str = implode(',',$a);

$str = '.$str.';

print $str;

?


I will print out:

'string1','string2'

--

Thodoris

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[TK]

At 05:53 PM 7/8/2008, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.

I may have found my answer (in the PHP manual, under PDO-prepare().  Go 
figure!):

You cannot bind multiple values to a single named parameter in, for example, 
the IN() clause of an SQL statement.

Perhaps this means it cannot be done.

- TK


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[TK]

At 03:23 AM 7/10/2008, Thodoris wrote:
Why don't you work this around. Since you may do anything with strings in php 
using the (.) operator try this:

?php
$a = array('string1','string2');
$str = implode(',',$a);
$str = '.$str.';
print $str;
?

Thanks, but this is missing the entire point of my question, which is *can it 
be done with PDO prepared statements*.  These solutions you're proposing do not 
involve PDO's prepared statements.  I already know how to do SQL queries by 
hand.

Anyway, per my latest message, I found where in the PHP manual it implies that 
PDO prepared statements cannot be used with the MySQL IN function, which speaks 
to my original question.

Thanks for trying!

- TK 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[Dee Ayy]

As you already know, you will dynamically create the $contents.

See if you can dynamically create the entire prepare statement which
includes the $contents, as well as dynamically create the bindValue
statement; then see if you can eval those dynamically created statements.

$commandPrepare = '$stmt = $pdo-prepare(\'
 select *
 from mytable
 where myfield IN ('.$dynamicallyCreatedContents.')\'
);';
eval($commandPrepare);

$commandBind = '$stmt-bindValue...  {escape quotes as needed -- I haven't
tested it}
eval($commandBind);
$stmt-execute();

On Tue, Jul 8, 2008 at 11:55 AM, TK [EMAIL PROTECTED] wrote:

 I'd like to use a PDO prepared statement to perform a MySQL query that uses
 the IN function.

 I.e.:
 $stmt = $pdo-prepare('
   select *
   from mytable
   where myfield IN (:contents)
 );
 $stmt-bindValue(':contents', $contents);
 $stmt-execute();

 Here's the problem:

 If $contents is set to a single value, everything's fine:
   $contents = 'mystring';

 How can I include multiple values in here?  If $contents is set to an
 array, PHP throws an Array to string conversion notice.
 i.e. $contents = array('mystring1', 'mystring2');

 If $contents is set to a comma-separated list, no matches are returned
 (probably because the entire list is being passed to MySQL as a single
 value, not multiple values).
 I.e. $contents = 'mystring1,mystring2';

 What's the proper way to do this?  Can it be done?  (Note that I do not
 know how many elements will be in $contents ahead of time, so something like
 IN (:contents1, :contents2) wouldn't make sense.)

 Thanks for any help!

 - TK


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[Thodoris]

At 04:16 PM 7/8/2008, Philip Thompson wrote:

On Jul 8, 2008, at 11:55 AM, TK wrote:


I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo-prepare('
 select *
 from mytable
 where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
 $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an Array to string conversion notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)


Thanks for any help!

- TK
  

$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = ' . implode (',', $contents) . ';

Hope that helps,

~Philip




Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).

- TK 



  
Perhaps the implode suggested above is the solution for the prepared statement. 
I think that the problem is that you cannot bind a value with an array.

So you have  to implode the array into a string before binding it to a value. 
The other goes as you suggested:

$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Where contents is:

$pre_contents = array('mystring1', 'mystring2');

$contents = implode(',',$pre_contents);

or 


$contents = 'mystring';

--
Thodoris

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[TK]

At 03:21 AM 7/9/2008, Thodoris wrote:
At 04:16 PM 7/8/2008, Philip Thompson wrote:
On Jul 8, 2008, at 11:55 AM, TK wrote:
I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.

I.e.:
$stmt = $pdo-prepare('
 select *
 from mytable
 where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
 $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an Array to string conversion notice.
i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)
  
$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.

$ins = ' . implode (',', $contents) . ';

Hope that helps,

Thanks.  That's how to use the IN function in the first place, which I 
already know.  What I was asking about was how to do this using PDO and 
prepared statements.  It's the PDO prepared statement functionality that's 
got me stuck.  The issue is that I can't figure out how to bindParam or 
bindValue in this situation, where there is potentially a list of values (of 
arbitrary size).
  
Perhaps the implode suggested above is the solution for the prepared 
statement. I think that the problem is that you cannot bind a value with an 
array.
So you have  to implode the array into a string before binding it to a value. 
The other goes as you suggested:

$stmt = $pdo-prepare('
select *
from mytable
where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Where contents is:

$pre_contents = array('mystring1', 'mystring2');

$contents = implode(',',$pre_contents);

or 
$contents = 'mystring';


As per my original question, that does not work:

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';

Binding a comma-separated list (i.e. what implode creates) does not do what is 
wanted here - it appears instead to treat the whole list as one entry, i.e. 
it's like doing this:
   where myfield IN ('mystring1,mystring2')
which is obviously not achieving the desired result of:
   where myfield IN ('mystring1','mystring2')

Hence, my original question!  Is there a way to accomplish this with PDO and 
prepared statements?

- TK


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO prepared statements and value list for MySQL IN

[TK]

I'd like to use a PDO prepared statement to perform a MySQL query that uses the 
IN function.

I.e.:
$stmt = $pdo-prepare('
   select *
   from mytable
   where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
   $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to an array, 
PHP throws an Array to string conversion notice.
i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are returned 
(probably because the entire list is being passed to MySQL as a single value, 
not multiple values).
I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do not know 
how many elements will be in $contents ahead of time, so something like IN 
(:contents1, :contents2) wouldn't make sense.)

Thanks for any help!

- TK


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[Philip Thompson]

On Jul 8, 2008, at 11:55 AM, TK wrote:

I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.


I.e.:
$stmt = $pdo-prepare('
  select *
  from mytable
  where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
  $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an Array to string conversion notice.

i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).

I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)


Thanks for any help!

- TK



$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.


$ins = ' . implode (',', $contents) . ';

Hope that helps,

~Philip


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO prepared statements and value list for MySQL IN

[TK]

At 04:16 PM 7/8/2008, Philip Thompson wrote:
On Jul 8, 2008, at 11:55 AM, TK wrote:

I'd like to use a PDO prepared statement to perform a MySQL query  
that uses the IN function.

I.e.:
$stmt = $pdo-prepare('
  select *
  from mytable
  where myfield IN (:contents)
);
$stmt-bindValue(':contents', $contents);
$stmt-execute();

Here's the problem:

If $contents is set to a single value, everything's fine:
  $contents = 'mystring';

How can I include multiple values in here?  If $contents is set to  
an array, PHP throws an Array to string conversion notice.
i.e. $contents = array('mystring1', 'mystring2');

If $contents is set to a comma-separated list, no matches are  
returned (probably because the entire list is being passed to  
MySQL as a single value, not multiple values).
I.e. $contents = 'mystring1,mystring2';

What's the proper way to do this?  Can it be done?  (Note that I do  
not know how many elements will be in $contents ahead of time, so  
something like IN (:contents1, :contents2) wouldn't make sense.)

Thanks for any help!

- TK


$contents = array('string1', 'string2', 'string3');
$ins = implode (',', $contents);
$sql = SELECT * FROM `table` WHERE `field` IN ($ins);

I'm not sure if the IN function only works on numeric values or if it  
also works on strings as well. If contents contains strings, you may  
need to put single quotes around each item. If so, change the above  
to.

$ins = ' . implode (',', $contents) . ';

Hope that helps,

~Philip


Thanks.  That's how to use the IN function in the first place, which I already 
know.  What I was asking about was how to do this using PDO and prepared 
statements.  It's the PDO prepared statement functionality that's got me stuck. 
 The issue is that I can't figure out how to bindParam or bindValue in this 
situation, where there is potentially a list of values (of arbitrary size).

- TK 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] [PDO] Cannot execute queries while other unbuffered queries are active

[Fabian Golle]

Hey,

I'm using a PDO-MySQL in a class of my Accounting-Framework. The problem: if
I call my function query(), see below, multiple times, I get the error:

 

Array ( [0] = HY000 [1] = 2014 [2] = Cannot execute queries while other
unbuffered queries are active. Consider using PDOStatement::fetchAll().
Alternatively, if your code is only ever going to run against mysql, you may
enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY
attribute. ) 1

 

protected function query($query) {

  try {

if (preg_match(@[EMAIL PROTECTED], $query)) {

  $sth = $this-link-prepare($query) or
die(print_r($this-link-errorInfo()));

  $sth-execute();



  // Get RowCount

  $rc = 0;

  while($count = $sth-fetch(PDO::FETCH_ASSOC)) $rc++;

  $this-count[(string)$sth] = $rc;

  $sth-execute();

  return $sth;

} else {

  $this-link-query($query);

  return true;

}

  } catch (Exception $e) {

$dbh-rollBack();

echo DATABASE OPERATION FAILED:  . $e-getMessage();

exit;

  }

}

 

The Solution described in the official manual is: 

 

$db-setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

 

My Connection function (which also includes the solution):

 

private function connect() {

  $this-link = new
PDO('mysql:host=localhost;dbname=micronax_mymicronax', mymicronax,
l{7kD01eG-B[);

  $this-link-setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

  return true;

}

 

$link is defined as: protected $link = false;

 

 

But I still get the damn error-message. What's going wrong here?

 

Other possibilities, ex. Adding the MYSQL_ATTR_USE_BUFFERED_QUERY to new
PDO() or adding it to prepare($query) doesn't show affect.

 

Please help me,

 

Greetings from Germany and happy easter,

 

--

Fabian Golle

Salisweg 22e | 63454 Hanau

Tel.: +49 6181 26644

Mobil: +49 172 8183994

Web: http://www.micronax.de

Email: [EMAIL PROTECTED]

(c) Micronax Systems 2007. All rights reserved

 

[PHP-DB] PDO::TIME_ZONE and or Initializing Query

[Paul Rogers]

It would be helpful if the time zone of a session could be specified at
the time of the connection. This would avoid the overhead of a separate
database query and round-trip to the database server when a single,
non-default time zone is needed for the duration of the connection.

Naturally there is the option of using GMT and dealing with the time
zone offsets entirely in PHP or at some other layer. However, many large
databases systems support connection-specific time zones and I've found
them to be quite useful.

It may also be useful as a way to set the time zone after the connection
is made using setAttribute.

Since several supported systems are not time-zone aware, such as Sqlite,
the attribute would not always have an effect. Or it could be
implemented as a server-specific attribute such as MYSQL_TIME_ZONE or
PGSQL_TIME_ZONE.

If a TIME_ZONE attribute seems inappropriate then another idea is to
provide an optional, initialization query when creating the PDO object.
For example, a SET TIME ZONE 'CST6CDT' query could be issued to set
the time zone immediately after the connection is made. Such a mechanism
may have other uses for queries that do not require processing the
response.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO::TIME_ZONE and or Initializing Query

[Chris]

Paul Rogers wrote:

It would be helpful if the time zone of a session could be specified at
the time of the connection. This would avoid the overhead of a separate
database query and round-trip to the database server when a single,
non-default time zone is needed for the duration of the connection.

Naturally there is the option of using GMT and dealing with the time
zone offsets entirely in PHP or at some other layer. However, many large
databases systems support connection-specific time zones and I've found
them to be quite useful.

It may also be useful as a way to set the time zone after the connection
is made using setAttribute.

Since several supported systems are not time-zone aware, such as Sqlite,
the attribute would not always have an effect. Or it could be
implemented as a server-specific attribute such as MYSQL_TIME_ZONE or
PGSQL_TIME_ZONE.

If a TIME_ZONE attribute seems inappropriate then another idea is to
provide an optional, initialization query when creating the PDO object.
For example, a SET TIME ZONE 'CST6CDT' query could be issued to set
the time zone immediately after the connection is made. Such a mechanism
may have other uses for queries that do not require processing the
response.


I think the problem here is that each database type handles it all 
differently, hence why you have to do it yourself.


If you want to put it to the developers, join the -internals list and 
discuss the idea there.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO and Oracle

[Roberto Mansfield]

Folks,

I was looking at rewriting some old DB classes using PDO with the Oracle
driver, but I'm a bit confused about the current state of things. Some
docs said the PDO Oracle driver was experimental and subject to change.
I also had a hard time trying to get the driver to compile against
Oracle's instant client. Are people using PDO and Oracle in production
environments? Am I just being dense and unable to find a guide to
compile against the instant client?  Thanks for the input.

Roberto

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] pdo::mysql and unbuffered queries

[Chris]

I tried using pdo (www.php.net/pdo) as it would be nice for my projects 
to be portable to another database systems, but quickly ran into problems.


The lesser important: One time, there was a simple typo in one of my 
queries and I got an error that php is not able to save session 
variables at the given path - I'm not joking! I worked several hours to 
solve that session problem just to realize the query had a bracket to 
few. I already got other errors in my queries where I was given a 
correct error trace, this is quite confusing.


Can't comment on that one but if you can prove it in a small test 
script, post a bug report on http://bugs.php.net



The bigger important: I was not able to build nested queries.


Post your code. I doubt that the guys that developed PDO would stop 
something like this.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] pdo::mysql and unbuffered queries

[Thomas Tschernich]

Hello there,

I tried using pdo (www.php.net/pdo) as it would be nice for my projects to 
be portable to another database systems, but quickly ran into problems.


The lesser important: One time, there was a simple typo in one of my queries 
and I got an error that php is not able to save session variables at the 
given path - I'm not joking! I worked several hours to solve that session 
problem just to realize the query had a bracket to few. I already got other 
errors in my queries where I was given a correct error trace, this is quite 
confusing.


The bigger important: I was not able to build nested queries. For example: 
Imagine you are running a bulletin board, and you have to select 10 posts in 
a thread. To each post, there is another information which is needed to be 
resolved from the database, so I have 1 more query for each of these posts. 
This is possible only with one of two workarounds .. I can use 
pdoStatement::fetchall() to instantly load all result rows into one array 
and afterwards looping through this array. After years of programming in 
classic php-mysql I am not used to this method at all and I do not think 
this is nice programming in concerns of memory usage as this array may 
become very big. The other option is to force queries to be buffered via the 
constantPDO::MYSQL_ATTR_USE_BUFFERED_QUERY, but then I would gave up 
independence as such code is not anymore compatible to other dbms than 
mysql.

In simple words: Can I have best of both worlds?

Big thanks in advance,
Thomas Tschernich 


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO Contact ?

[Nicolas Boiteux]

Hello,

I'm leading the PhpMyObject (PMO) Project:
official website:
http://pmo.developpez.com

It's an object abstraction layer that permits to convert data from PDO 
to unitary PHP object (not row).


Example:

|   require_once 
http://www.php.net/manual/fr/function.require-once.php(|||core||/||PMO_MyController||.||php|||)|;|

|$controler| |=| |new| PMO_MyController()|;|
|$||map| |=| |$controler||-queryController|(|||SELECT|| 
||*|| ||FROM|| ||employe||,||parking|| ||WHERE|| 
||employe||.||id_employe||=||parking||.||id_employe|| ||limit|| ||20|| ||;|||)|;|

|while| (|$||result| |=| |$map||-fetchMap|())|{|
echo 
http://www.php.net/manual/fr/function.echo.php(|$result||[||'||employe||'||]||-nom|)|;|
echo 
http://www.php.net/manual/fr/function.echo.php(|$result||[||'||parking||'||]||-numero_place|)|;|
|}||



Like the project progressed well and that there are many possibilities, 
I wish to know who i can contact from PDO Team to submit evolutions ?


Regards,
Nicolas

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO Contact ?

[Chris]

I wish to know who i can contact from PDO Team to submit evolutions ?


No particular person. I think Wez was in charge when it was being built 
but it's all integrated into the core php now - so join the -internals 
list and make a proposal/suggestion.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO and MS Sql Server

[Bruce Cowin]

I'm using PHP 5.1.  The documentation for PDO doesn't list MS Sql server as one 
of the drivers that support PDO but there is a php_pdo_mssql.dll which seems to 
work so I'm using that.

I need to get the id of a new record just inserted.  I can't use lastInsertId() 
as I get a message saying it's not supported.  So I've created a stored proc 
that returns the id just created (code below).  The insert works fine but the 
$emailid variable is not populated.  I can run the stored proc in query 
analyzer and it outputs the id correctly so I know the stored proc works.  All 
the examples I see return strings, not sure if that has anything to do with it. 
 And as for the parameter length for the output parameter, I've tried nothing 
as well as 9.

$stmt = $this-dbh-prepare(exec usp_EmailInsert :projectid, :mailfrom, 
:mailto, :mailcc, :subject, :body, :mimefilename, :emailid);
$stmt-bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt-bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt-bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt-bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt-bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt-bindParam(':body', $body, PDO::PARAM_LOB);
$stmt-bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);  

$stmt-bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
$stmt-execute();

echo \nemailid = $emailid\n;



Anyone have any ideas?  Thanks.


Regards,

Bruce

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and MS Sql Server

[Chris]

Bruce Cowin wrote:

I'm using PHP 5.1.  The documentation for PDO doesn't list MS Sql server as one 
of the drivers that support PDO but there is a php_pdo_mssql.dll which seems to 
work so I'm using that.

I need to get the id of a new record just inserted.  I can't use lastInsertId() 
as I get a message saying it's not supported.  So I've created a stored proc 
that returns the id just created (code below).  The insert works fine but the 
$emailid variable is not populated.  I can run the stored proc in query 
analyzer and it outputs the id correctly so I know the stored proc works.  All 
the examples I see return strings, not sure if that has anything to do with it. 
 And as for the parameter length for the output parameter, I've tried nothing 
as well as 9.

$stmt = $this-dbh-prepare(exec usp_EmailInsert :projectid, :mailfrom, :mailto, 
:mailcc, :subject, :body, :mimefilename, :emailid);
$stmt-bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt-bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt-bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt-bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt-bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt-bindParam(':body', $body, PDO::PARAM_LOB);
$stmt-bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);  
 
$stmt-bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
$stmt-execute();


prepared statements are for ingoing queries, they can't put results from 
that query into a binded parameter.


That is, when you bind a parameter it only works for the query TO the 
database, they are not filled in for outgoing results.


Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples 
here:


http://www.php.net/manual/en/function.PDO-prepare.php

--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and MS Sql Server

[Bruce Cowin]

Thanks for replying.  According to the PDO doco: If the database driver 
supports it, you may also bind parameters for output as well as input.  So 
maybe this driver doesn't support it?  I don't know.  I'll try and get the 
stored proc to return the value, but not sure how I'll capture that yet.  If 
that doesn't work, I'll just do a select @@identity.

Thanks again.



Regards,

Bruce

 Chris [EMAIL PROTECTED] 6/07/2007 12:44:34 p.m. 
Bruce Cowin wrote:
 I'm using PHP 5.1.  The documentation for PDO doesn't list MS Sql server as 
 one of the drivers that support PDO but there is a php_pdo_mssql.dll which 
 seems to work so I'm using that.
 
 I need to get the id of a new record just inserted.  I can't use 
 lastInsertId() as I get a message saying it's not supported.  So I've created 
 a stored proc that returns the id just created (code below).  The insert 
 works fine but the $emailid variable is not populated.  I can run the stored 
 proc in query analyzer and it outputs the id correctly so I know the stored 
 proc works.  All the examples I see return strings, not sure if that has 
 anything to do with it.  And as for the parameter length for the output 
 parameter, I've tried nothing as well as 9.
 
 $stmt = $this-dbh-prepare(exec usp_EmailInsert :projectid, :mailfrom, 
 :mailto, :mailcc, :subject, :body, :mimefilename, :emailid);
 $stmt-bindParam(':projectid', $projectid, PDO::PARAM_INT);
 $stmt-bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
 $stmt-bindParam(':mailto', $to, PDO::PARAM_STR, 500);
 $stmt-bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
 $stmt-bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
 $stmt-bindParam(':body', $body, PDO::PARAM_LOB);
 $stmt-bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);
 
 $stmt-bindParam(':emailid', $emailid, PDO::PARAM_INT, 9);
 $stmt-execute();

prepared statements are for ingoing queries, they can't put results from 
that query into a binded parameter.

That is, when you bind a parameter it only works for the query TO the 
database, they are not filled in for outgoing results.

Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples 
here:

http://www.php.net/manual/en/function.PDO-prepare.php 

-- 
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO and MS Sql Server

[Chris]

Bruce Cowin wrote:

Thanks for replying.  According to the PDO doco: If the database driver supports it, you may 
also bind parameters for output as well as input.  So maybe this driver doesn't support it?  
I don't know.  I'll try and get the stored proc to return the value, but not sure how I'll capture 
that yet.  If that doesn't work, I'll just do a select @@identity.


Ah didn't know that :)

Maybe check with the php-general list to see if anyone else had similar 
experience with mssql  the pdo driver.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO prepared statements sometimes returns empty resultset

[Emil Ivanov]

Hi,

I'm trying to get some data form a MySQL db using PDO and prepared 
statement, but I noticed that sometimes it fetches an empty result set, 
but sometimes does what it's supposed to do - returns the whole result set.


The environment is WinXP SP2 Home, Apache 2.2.4, PHP 5.2.1 and MySQL 5.0.27.

Here is some code:

$db = new PDO(
  'mysql:host=localhost;dbname=name_of_db',
  'user_name',
  'password',
  array (
 PDO::ATTR_PERSISTENT = true
  )
);
$db-query('SET CHARACTER SET utf8');
$stmt = $db-prepare(CALL sp_get_all_currencies(:lang_id));
$stmt-bindValue(':lang_id', $currenctLanguageId, PDO::PARAM_INT);
$stmt-execute();
$currencies = $stmt-fetchAll();

So here, $currencies sometimes is an empty array, and after a refresh of 
the browser it populates.


I also noticed that it works ok with $db-query(), but query doesn't 
have that fancy param binding (I think).


Is there some caching or something... Is this a bug in PHP or I'm not 
getting the picture right?


Regards,
Emil Ivanov

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO setAttribute return value

[Jerry Schwartz]

Is it my imagination, or does setAttribute return FALSE on success and TRUE 
on failure? I've tested it with getAttribute, and it definitely seems to 
work the reverse of what the documentation states.

I'm using MySQL, and I am setting the error handling. 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO Exception Handling Question

[Jerry Schwartz]

I hope by now you figured this out, but you have to use $dbh-setAttribute() 
to enable exception handling for anything but the initial object creation.

Worse yet, as of 2007/04/20 the return values of setAttribute are reversed. 
It returns TRUE on failure and FALSE on success.

Rodney Topor [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
I did some more checking.  If there is an error in the new PDO(...)
 constructor call, then it does get into the catch block and print an
 appropriate error message.  (Good.  Perhaps I was previously mistaken.)

 But if there is an error in the SELECT query (either syntax error, or
 reference to missing table), then no exception is raised, and control
 just continues in the try block with a null result from the query.
 Is this the intended behaviour?

 On Sun, 2 Apr 2006, chris smith wrote:

 On 4/2/06, Rodney Topor [EMAIL PROTECTED] wrote:
 I've just started using PDO with PHP 5.1 and MySQL 5.0.  Everything
 seems to work fine, except for one issue with the following example
 from the PHP manual.

 ?php
 try {
 $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
 foreach ($dbh-query('SELECT * from FOO') as $row) {
   print_r($row);
 }
 $dbh = null;
 } catch (PDOException $e) {
 print Error!:  . $e-getMessage() . br/;
 die();
 }
 ?

 If an error occurs in creating the new PDO instance, or in issuing the
 PDO query, then a PDOException should be raised.  But nothing is ever
 printed.  I've tried running this script, forcing an exception, both
 from the command line and from the server.  But nothing is ever
 printed.  What do I have to do to see the printed string in the
 catch-block?

 Do you mean it doesn't print anything or it doesn't get into the catch 
 block?

 ie it prints Error!:br/ or it doesn't get into that section at all?

 --
 Postgresql  php tutorials
 http://www.designmagick.com/
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO stored procedures php5.1 mysql5

[gxjw-3301]

Hello,

I've some problems with calling stored procedures.
When I try to execute the demonstration example on php.net I get no result, but
it works with normal SQL-commandqueries

software:  MAMP-package: php 5.1.6, mysql 5.0.19, apache 2.0.59

my stored procedure is for this example returns a string:
CREATE PROCEDURE stringi(OUT result VARCHAR(30)) BEGIN SELECT text INTO result
FROM checker WHERE id = 1;

it works with sql queries like:  'CALL stringi(@a)' - 'SELECT @a'



so the stored procedure should be working correctly, or did i miss something?
Thanks ahead for any advise!





the first code is the one from php.net that doesn't work:
code-output: procedure returned mysql

 try{
$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
 

$stmt = $dbh-prepare(CALL stringi(?));
$stmt-bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt-execute();

print procedure returned $return_value\n;


 
echo $dbh-getAttribute(PDO::ATTR_DRIVER_NAME);
  
 }
 catch (PDOException $e) {
   print Error!:  . $e-getMessage() . br/;
   die();
}



this is the example described above that works
output: Array ( [EMAIL PROTECTED] = baer [0] = baer ) mysql


try{
$dbh = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');

$dbh-query('CALL stringi(@a)');

foreach($dbh-query('SELECT @a') as $foo)
print_r($foo).'/ br';
   
echo $dbh-getAttribute(PDO::ATTR_DRIVER_NAME);
  
 }
 catch (PDOException $e) {
   print Error!:  . $e-getMessage() . br/;
   die();
}

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] pdo parameter in limit?

[Patrick Felt]

i'm running php PHP Version 5.1.6-pl6-gentoo and have run into a bit
of a snag with pdo prepared statements and parameters.  we use
parameters in all our queries, but just recently tried to add limits to
some of the queries so we can do paging.  when attempting to add a
parameter to the limit section of the sql i get the following error
message and a failed query.

Warning: PDO::prepare() [function.PDO-prepare
http://pat.q90bo.com/main/admin/function.PDO-prepare]:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '? , ?' at line 2 in

a sample query would be

 SELECT * FROM tablename WHERE somecol=1 LIMIT :start, :count

when i remove the :count parameter the error message changes to .
near '?' at line 2 in so i know that the ?, ? map to the two
parameters.  i know that there are cases where you can't use parameters
(which don't seem to be clearly documented anywhere), is this one of
them?  if not, is there some way to do such a thing or would i just need
to resort to something like mysql_escape_string($start) so that the data
(which comes from users) is properly escaped?

thanks,

pat

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] PDO, fetch prepared statement

[Chris]

Rudi Worm wrote:

Hi

Using PDO to prepare a query, bind som values to it (using ? Placeholders)
and then executing it - how do i get the actual query afterwards?  With the
correct inserted values.


Use your system database logs. PDO doesn't give you the opportunity to 
grab them.


--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO, fetch prepared statement

[Rudi Worm]

Hi

Using PDO to prepare a query, bind som values to it (using ? Placeholders)
and then executing it - how do i get the actual query afterwards?  With the
correct inserted values.

It is needed logging purposes.

I haven't been able to find any method or property that gives me this
information.

Thanks.

-- 
Best Regards

Rudi W.
Systemudvikler, ZCE
WorldWeb Interactive ApS

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] PDO and Exceptions

[Vandegrift, Ken]

Thanks.

That was my issue. I had to separate each insert instead of grouping
them together and executing the query.  Exception is now thrown if a
primary key violation occurs and entire transaction is rolled back.


Ken Vandegrift

-Original Message-
From: Chris [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, June 21, 2006 5:39 PM
To: Vandegrift, Ken
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] PDO and Exceptions

Vandegrift, Ken wrote:
 Good Afternoon,
  
 I have an instance of a PDO object set to throw a PDOException on 
 errors.  However, it does not appear to do this when a primary key 
 violation error occurs on an insert sql statement.  An exception 
 should be thrown in this case correct?
  
 The insert statements are part of a transaction but I am unable to 
 rollback the transaction on error.
  
 Any ideas would be greatly appreciated.
  
 Code sample:
  
 /** Excerpt from DB connection class **/ $this-db = new 
 PDO('odbc:DBNAME', $userid, $pwd); 
 $this-db-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
 /**
   * Updates order information in the database.
   * Called from Order::dbSaveOrder
   * @return boolean true on success
   * @access protected
   */
  protected function dbUpdateOrder() {
  
   // VARIABLES TO TRACK PROPER SQL FORMATTING
   $count = count($this-attributes);
   $i = 1;
  
   // BUILD ORDER TABLE UPDATE QUERY
   $sql = UPDATE  . ORDER_TABLE .  SET ;
   foreach ($this-attributes as $attribute = $value) {
$attribute = strtoupper($attribute);
$sql  .= (is_string($value) || is_null($value)) ? {$attribute}
=
 '{$value}' : {$attribute} = {$value};
if ($i  $count) {
 $i++;
 $sql .= , ;
}
   }
   $sql .=  WHERE  . ORDER_TABLE_ID .  = :orderid;;
  
   try {
$this-db-beginTransaction();
$stmt = $this-db-prepare($sql);
$stmt-execute(array(':orderid' = $this-attributes['order_id']));
  
 / ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT 
 TRIGGER AN EXCEPTION ON DUPLICATE ENTRIES /
  
// SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
if (!empty($this-gcnums)) {
 $sql = null;
 foreach ($this-gcnums as $itemid = $gcnumbers) {
   foreach ($gcnumbers as $gcnum) {
 $sql .= INSERT INTO GIFTCARD_RECORD  .
 ( .
 ORDER_ID,  .
 ITEM_ID,  .
 GC_NUM  .
 )  .
 VALUES .
 ( .
 {$this-__get('order_id')},  .
 {$itemid},  .
 '{$gcnum}' .
 );;
   } // End Inner FOREACH
} // End Outer FOREACH
$stmt = $this-db-prepare($sql);
  $stmt-execute();
}
$this-db-commit();
$stmt = null;
return true;
   }catch (PDOException $e) {
$this-db-rollBack();
Log::write(LOG_DIR, 'order',
  \r Order::dbUpdateOrder \r .
  [UPDATE SQL] =  . $sql . \r .
  [DB ERROR] =  . $e-getMessage() . \r .
  **
);
$this-error = 'The system has encountered an error and is unable 
 to update this order. ' .
  'Please contact the system administrator.';
$stmt = null;
return false;
   }
  }
  
 Ken Vandegrift
  
 

I can't see a try in the second part. Wouldn't you need:

try {
   update query
   try {
 insert query
   } catch exception
} catch exception

?

I could be on the completely wrong track but I think you need a try /
catch for each part.
--
Postgresql  php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] PDO and Exceptions

[Vandegrift, Ken]

Good Afternoon,
 
I have an instance of a PDO object set to throw a PDOException on
errors.  However, it does not appear to do this when a primary key
violation error occurs on an insert sql statement.  An exception should
be thrown in this case correct?
 
The insert statements are part of a transaction but I am unable to
rollback the transaction on error.
 
Any ideas would be greatly appreciated.
 
Code sample:
 
/** Excerpt from DB connection class **/
$this-db = new PDO('odbc:DBNAME', $userid, $pwd);
$this-db-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
/**
  * Updates order information in the database.
  * Called from Order::dbSaveOrder
  * @return boolean true on success
  * @access protected
  */
 protected function dbUpdateOrder() {
 
  // VARIABLES TO TRACK PROPER SQL FORMATTING
  $count = count($this-attributes);
  $i = 1;
 
  // BUILD ORDER TABLE UPDATE QUERY
  $sql = UPDATE  . ORDER_TABLE .  SET ;
  foreach ($this-attributes as $attribute = $value) {
   $attribute = strtoupper($attribute);
   $sql  .= (is_string($value) || is_null($value)) ? {$attribute} =
'{$value}' : {$attribute} = {$value};
   if ($i  $count) {
$i++;
$sql .= , ;
   }
  }
  $sql .=  WHERE  . ORDER_TABLE_ID .  = :orderid;;
 
  try {
   $this-db-beginTransaction();
   $stmt = $this-db-prepare($sql);
   $stmt-execute(array(':orderid' = $this-attributes['order_id']));
 
/ ABOVE CODE WORKS WITHOUT ISSUE - THIS NEXT BLOCK DOES NOT TRIGGER
AN EXCEPTION ON DUPLICATE ENTRIES /
 
   // SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
   if (!empty($this-gcnums)) {
$sql = null;
foreach ($this-gcnums as $itemid = $gcnumbers) {
  foreach ($gcnumbers as $gcnum) {
$sql .= INSERT INTO GIFTCARD_RECORD  .
( .
ORDER_ID,  .
ITEM_ID,  .
GC_NUM  .
)  .
VALUES .
( .
{$this-__get('order_id')},  .
{$itemid},  .
'{$gcnum}' .
);;
  } // End Inner FOREACH
   } // End Outer FOREACH
   $stmt = $this-db-prepare($sql);
 $stmt-execute();
   }
   $this-db-commit();
   $stmt = null;
   return true;
  }catch (PDOException $e) {
   $this-db-rollBack();
   Log::write(LOG_DIR, 'order',
 \r Order::dbUpdateOrder \r .
 [UPDATE SQL] =  . $sql . \r .
 [DB ERROR] =  . $e-getMessage() . \r .
 **
   );
   $this-error = 'The system has encountered an error and is unable to
update this order. ' .
 'Please contact the system administrator.';
   $stmt = null;
   return false;
  }
 }
 
Ken Vandegrift