pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedUse of pg_escape_string()
3+ messages / 3 participants
[nested] [flat]
* Use of pg_escape_string()
@ 2009-11-22 19:22 Sylvain Racine <syracine@sympatico.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Sylvain Racine @ 2009-11-22 19:22 UTC (permalink / raw)
To: pgsql-php
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Use of pg_escape_string()
@ 2009-11-22 19:44 Raymond O'Donnell <rod@iol.ie>
parent: Sylvain Racine <syracine@sympatico.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Raymond O'Donnell @ 2009-11-22 19:44 UTC (permalink / raw)
To: Sylvain Racine <syracine@sympatico.ca>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Use of pg_escape_string()
@ 2009-11-23 17:31 Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
parent: Raymond O'Donnell <rod@iol.ie>
0 siblings, 0 replies; 3+ messages in thread
From: Eric Chamberlain @ 2009-11-23 17:31 UTC (permalink / raw)
To: rod@iol.ie <rod@iol.ie>; +Cc: Sylvain Racine <syracine@sympatico.ca>; pgsql-php
Adding an extra apostrophe is one of the ways you can escape another apostrophe with Postgre. I believe Postgre can use an extra apostrophe or a backslash... and the API call you're using just happens to elect using the extra 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 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 for the intended recipient(s) only. If you believe that it has been sent to you in error, please notify the sender by reply e-mail and delete the message. Any disclosure, copying, distribution or use of this information by someone other than the intended recipient(s) is prohibited and may be unlawful.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-11-23 17:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-22 19:22 Use of pg_escape_string() Sylvain Racine <syracine@sympatico.ca>
2009-11-22 19:44 ` Raymond O'Donnell <rod@iol.ie>
2009-11-23 17:31 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox