Whats does pg_escape_string exactly do?

Question

I'm using the following script which takes the data from a html form and stores in a Postgres DB. There is this pg_escape_string function which stores the value from the form to the php variable. Searching the web throughout, I found that pg_escape_string escapes a string for insertion into the database. I'm not much clear on this. What does it actually escaping? What actually happens when its said that a string is escaped?

<html>
   <head></head>
   <body>       

 <?php
 if ($_POST['submit']) {
     // attempt a connection
     $dbh = pg_connect("host=localhost dbname=test user=postgres");
     if (!$dbh) {
         die("Error in connection: " . pg_last_error());
     }

     // escape strings in input data
     $code = pg_escape_string($_POST['ccode']);
     $name = pg_escape_string($_POST['cname']);

     // execute query
     $sql = "INSERT INTO Countries (CountryID, CountryName) VALUES('$code', '$name')";
     $result = pg_query($dbh, $sql);
     if (!$result) {
         die("Error in SQL query: " . pg_last_error());
     }

     echo "Data successfully inserted!";

     // free memory
     pg_free_result($result);

     // close connection
     pg_close($dbh);
 }
 ?>       

    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
      Country code: <br> <input type="text" name="ccode" size="2">  
      <p>
      Country name: <br> <input type="text" name="cname">       
      <p>
      <input type="submit" name="submit">
    </form> 

   </body>
 </html>

Ryan Culpepper · Answer 1 · 2011-09-01T06:11:59.207

Consider the following code:

$sql = "INSERT INTO airports (name) VALUES ('$name')";

Now suppose that $name is "Chicago O'Hare". When you do the string interpolation, you get this SQL code:

INSERT INTO airports (name) VALUES ('Chicago O'Hare')

which is ill-formed, because the apostrophe is interpreted as a SQL quote mark, and your query will error.

Worse things can happen, too. In fact, SQL injection was ranked #1 Most Dangerous Software Error of 2011 by MITRE.

But you should never be creating SQL queries using string interpolation anyway. Use queries with parameters instead.

$sql = 'INSERT INTO airports (name) VALUES ($1)';
$result = pg_query_params($db, $sql, array("Chicago O'Hare"));

What exactly it does is change 'Chicago O'Hare' to 'Chicago O''Hare' (exactly how it is escaped depends upon your database settings) — dsas, Aug 15 '14 at 09:59

score 1 · Answer 2 · answered Sep 01 '11 at 04:27

1

pg_escape_string() prevent sql injection in your code

answered Sep 01 '11 at 04:27

tttony

4,944
4
26
41

1

This is really old, but for anyone coming across this later, pg_escape_string() is one defense against SQL injection, but does not prevent it. Using prepared statements is better. (http://stackoverflow.com/questions/732561/why-is-using-a-mysql-prepared-statement-more-secure-than-using-the-common-escape) – Pandu Mar 30 '15 at 22:07

Whats does pg_escape_string exactly do?

2 Answers2

Linked