pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Use 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>
  2009-11-22 19:44 ` Re: Use of pg_escape_string() Raymond O'Donnell <rod@iol.ie>
  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: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   ` Re: Use of pg_escape_string() Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
  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-22 19:22 Use of pg_escape_string() Sylvain Racine <syracine@sympatico.ca>
  2009-11-22 19:44 ` Re: Use of pg_escape_string() Raymond O'Donnell <rod@iol.ie>
@ 2009-11-23 17:31   ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
  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