Thread: Use of pg_escape_string()
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->personnes);
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 .= '<div style="color:#FF0000">Can't connect to
database.</div>';
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 '<div>Query: '.$query.'</div>';
}
$this->query_ID = pg_query($this->link_ID, $query);
} else
{
if ($this->debug) {
print '<div>Query: '.$query.'</div>';
}
$this->query_ID = pg_query($this->link_ID, $query);
}
if (!$this->query_ID)
{
$this->err = pg_last_error($this->link_ID);
$this->error .= '<div style="color:#FF0000">Error in query sent
to database<br><br>'.$this->err."<br><br>\n";
$this->error .= "<u>Invalid SQL query:</u>:
<blockquote>$query</blockquote></div><br>";
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 "<br>";
}
}
//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'] = '<div
style="color:#FF0000">SQL Error: You try to insert values in table
without declare column name!</div>';
Error::thrown($arg,FALSE,TRUE);
}
foreach ($fields as $num =>$col) {
$query .= ($num ? ',': '').$col;
}
$query .= ') VALUES (';
if (empty($values)) {
$arg['type'] = 'php';
$arg['message'] = '<div
style="color:#FF0000">SQL Error: You try to insert data in table
without giving values!</div>';
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'] = '<div
style="color:#FF0000">SQL Error: The number of columns mismatches with
the number of values!</div>';
Error::thrown($arg,FALSE,TRUE);
}
$query .= $valueClause.');';
return $query;
}
}
Anybody have an idea?
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
Adding an extra apostrophe is one of the ways you can escape another apostrophe with Postgre. I believe Postgre can
usean extra apostrophe or a backslash... and the API call you're using just happens to elect using the extra apostrophe
overthe 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 quotes 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 some of which may be legally privileged. It is
forthe intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by
replye-mail and delete the message. Any disclosure, copying, distribution or use of this information by someone other
thanthe intended recipient(s) is prohibited and may be unlawful.