Use of pg_escape

Use of pg_escape_string()

am 22.11.2009 20:22:07 von Sylvain Racine

Hello,

I use to hear about to escape every variables who come from user in PHP.
Most programmers around me use MySQL with mysql_escape_string(). Because
I program with PostgreSQL, I take advantage to use pg_escape_string().
Everything goes well, up I entered data with apostrophe(').
pg_escape_string() escapes my apostrophe with another apostrophe ('').
My data are well store in database. No error... except that appears a
double apostrophe. This is not what I want.

Maybe something is wrong in my program. Here is a sample of what I use
to store data in table "personnes" which have two columns: firstname,
lastname. I remove database connection and construction of objects
Minute and Personnes.

Ex: Nathalie Prud'homme gives Nathalie Prud''homme...

$minute->personnes->firstname = $_POST["firstname"];
$minute->personnes->lastname = $_POST["lastname"];
$fields = array("firstname","lastname");

$query =
$GLOBALS['phpag']->db->record('personnes',$fields,$minute->p ersonnes);
if (!$GLOBALS['phpag']->db->query($query))
{
echo $GLOBALS['phpag']->db->error;
}

class db

{
function query($query, $offset=0, $num_rows=-1)
{
if (!$this->link_ID)
{
$this->error .= '

Can't connect to
database.

';
return FALSE;
}
$this->record = array();
$this->count = 0;

if (!$num_rows && $this->debug)
{
print 'number of lines limit =
'.$GLOBALS['phpag_info']['preferences']['phpag']['lines'];
$num_rows = $GLOBALS['phpag_info']['preferences']['phpag']['lines'];
}
if ($num_rows > 0)
{
$query .= ' LIMIT '.$num_rows.' OFFSET '.$offset;
if ($this->debug) {
print '

Query: '.$query.'

';
}

$this->query_ID = pg_query($this->link_ID, $query);
} else
{
if ($this->debug) {
print '

Query: '.$query.'

';
}

$this->query_ID = pg_query($this->link_ID, $query);
}

if (!$this->query_ID)
{
$this->err = pg_last_error($this->link_ID);
$this->error .= '

Error in query sent
to database

'.$this->err."

\n";
$this->error .= "Invalid SQL query::

$query

";
return FALSE;
}
else
{
for ($i = 0; $i < pg_numrows($this->query_ID); $i++)
{
$this->record[$i] = pg_fetch_array($this->query_ID,$i);

if ($this->debug)
{
echo 'Record #'.$i.' ';
print_r($this->record[$i]);
print "
";
}
}
//Calculate how many records
$this->count = ($this->record ? count($this->record): 0);
}

return $this->query_ID;
}

// Escape string if necessary
function slash ($text)
{
$text = pg_escape_string($text);
return $text;
}

function record($table,$fields,$values)
{
if (class_exists(get_class($values)))
$values=get_object_vars($values);
$query = 'INSERT INTO '.$table.'(';

if (empty($champs)) {
$arg['type'] = 'php';
$arg['message'] = '

style="color:#FF0000">SQL Error: You try to insert values in table
without declare column name!

';
Error::thrown($arg,FALSE,TRUE);
}
foreach ($fields as $num =>$col) {
$query .= ($num ? ',': '').$col;
}

$query .= ') VALUES (';

if (empty($values)) {
$arg['type'] = 'php';
$arg['message'] = '

style="color:#FF0000">SQL Error: You try to insert data in table
without giving values!

';
Error::thrown($arg,FALSE,TRUE);
}

$valueClause = '';
$id = 0;
foreach ($values as $num => $col) {
preg_match('/^id/',$num,$match);
if (!empty($match[0])) $id += 1;
if (empty($match[0])) { // Remove column beginning
with 'id...'
$valueClause .= ($valueClause ? ',' :
'').'\''.$this->slash($col).'\'';
}
}

if (count($fields) != (count($fields) - $id)) {
$arg['type'] = 'php';
$arg['message'] = '

style="color:#FF0000">SQL Error: The number of columns mismatches with
the number of values!

';
Error::thrown($arg,FALSE,TRUE);
}

$query .= $valueClause.');';

return $query;
}

}

Anybody have an idea?

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Use of pg_escape_string()

am 22.11.2009 20:44:48 von Rod

On 22/11/2009 19:22, Sylvain Racine wrote:
> Hello,
>
> I use to hear about to escape every variables who come from user in PHP.
> Most programmers around me use MySQL with mysql_escape_string(). Because
> I program with PostgreSQL, I take advantage to use pg_escape_string().
> Everything goes well, up I entered data with apostrophe(').
> pg_escape_string() escapes my apostrophe with another apostrophe ('').
> My data are well store in database. No error... except that appears a
> double apostrophe. This is not what I want.
>
> Maybe something is wrong in my program. Here is a sample of what I use
> to store data in table "personnes" which have two columns: firstname,
> lastname. I remove database connection and construction of objects
> Minute and Personnes.

Where is the INSERTed data coming from? - Is it coming from data
submitted by GET or POST? - if so, is magic_quotes_gpc turned on? If it
is, this could explain what you're seeing.

BTW, it's much better to use parametrised queries - look up
pg_query_params in the PHP docs. This looks after all quoting for you
automatically, and prevents SQL injection attacks.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Use of pg_escape_string()

am 23.11.2009 18:31:24 von Eric Chamberlain

Adding an extra apostrophe is one of the ways you can escape another apostr=
ophe with Postgre. I believe Postgre can use an extra apostrophe or a back=
slash... and the API call you're using just happens to elect using the extr=
a apostrophe over the backslash. If you look at the data inserted into the=
database is there only one apostrophe in your data? If so, that's what it=
is. If there's two it could be as the previous poster said and magic quot=
es is enabled.

Eric Chamberlain

On Nov 22, 2009, at 11:44 AM, Raymond O'Donnell wrote:

> On 22/11/2009 19:22, Sylvain Racine wrote:
>> Hello,
>>
>> I use to hear about to escape every variables who come from user in PHP.
>> Most programmers around me use MySQL with mysql_escape_string(). Because
>> I program with PostgreSQL, I take advantage to use pg_escape_string().
>> Everything goes well, up I entered data with apostrophe(').
>> pg_escape_string() escapes my apostrophe with another apostrophe ('').
>> My data are well store in database. No error... except that appears a
>> double apostrophe. This is not what I want.
>>
>> Maybe something is wrong in my program. Here is a sample of what I use
>> to store data in table "personnes" which have two columns: firstname,
>> lastname. I remove database connection and construction of objects
>> Minute and Personnes.
>
> Where is the INSERTed data coming from? - Is it coming from data
> submitted by GET or POST? - if so, is magic_quotes_gpc turned on? If it
> is, this could explain what you're seeing.
>
> BTW, it's much better to use parametrised queries - look up
> pg_query_params in the PHP docs. This looks after all quoting for you
> automatically, and prevents SQL injection attacks.
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>
> --
> Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-php

Confidentiality Notice: This e-mail may contain proprietary information som=
e of which may be legally privileged. It is for the intended recipient(s) o=
nly. If you believe that it has been sent to you in error, please notify th=
e sender by reply e-mail and delete the message. Any disclosure, copying, d=
istribution or use of this information by someone other than the intended r=
ecipient(s) is prohibited and may be unlawful.

--=20
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php