Skip site navigation (1) Skip section navigation (2)

Use of pg_escape_string()

From: Sylvain Racine <syracine(at)sympatico(dot)ca>
To: pgsql-php(at)postgresql(dot)org
Subject: Use of pg_escape_string()
Date: 2009-11-22 19:22:07
Message-ID: BLU0-SMTP3034F7FE7C5F0BEA0009A5FD9F0@phx.gbl (view raw or flat)
Thread:
Lists: 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?

Responses

pgsql-php by date

Next:From: Raymond O'DonnellDate: 2009-11-22 19:44:48
Subject: Re: Use of pg_escape_string()
Previous:From: Thom BrownDate: 2009-10-21 09:17:59
Subject: Re: PostgreSQL driver for Joomla review

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group