CODEWITHSUNDEEP
phppostgresqlquotes
Adam Andrews
Adam Andrews

Reputation: 7

Variable in Quotes in Postgres Query

This is probably a stupid question, but I have been Googling an answer for the better part of the day and can't get anywhere. I am trying to get the following bit of code to work, but can't find any help on how to properly format a prepared Postgres request in PHP. 

$foo = $_GET[bar];    

echo $foo; // 5555

//what I'm trying to do:
pg_prepare($dbconn,"table_query","SELECT Members FROM programs WHERE programID = '$1' ");
pg_execute($dbconn,"table_query", array($foo));

If I hardcode the statement with a value, it works fine, but only if I include the single quotes. I've tried just about every method I can find to escape the single quotes or append the quotes to the string, but all I can get are parsing errors.

What totally obvious thing am I missing?

Edit: Changed the snippet to clarify that the variable I am getting does not include quotes. Any method I where I try to add the quotes fails.

Upvotes: 0

Views: 1659

Answers (2)

Dario
Dario

Reputation: 2713

Let’s study a complete example. Suppose you got your value from a GET query which set the name pid. From your example query I expect the value to be the decimal representation of an integer, different from zero. It is a string, since nothing else can come from a GET query. 

 $pid = $_GET['pid'];

 // This is _very_ important.
 // Anything that comes from outside must be validated or sanitized.
 // FILTER_VALIDATE_INT refuses "0" too (correct if needed).
 if (filter_var($pid, FILTER_VALIDATE_INT) === false) {
   // Deal with invalid input 
 }

 $result = pg_query_params($dbconn,
     'SELECT Members FROM programs WHERE programID = $1',
     array($pid)
 );

pg_query_params binds $1 with $pid and quotes it correctly, while you cannot use double quotes around the statement because PHP would expand $1 incorrectly. There is no need to put quotes around $pid manually, because pg_query_params takes care of this. Furthermore, PostgreSQL accepts an integer value both with quotes and without them, so fumbling with quotes is pointless in this case.

Instead of using the traditional pg_ functions, you might use PDO, the PHP Database Object abstraction layer. In that case (disregarding possible options needed in your case):

  $dsn = 'pgsql:host='. $host .';port=5432;dbname='. $database;
  $dbh = new PDO($dsn, $user, $password);
  $dbh->prepare('SELECT Members FROM programs WHERE programID = ?');
  $result = $dbh->execute(array($pid)); // $pid as before

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You should be using prepared statements. This should solve your quoting problem and also remove a major risk of SQL injection. Try something like this:

$stmt = $conn->prepare("SELECT Members FROM programs WHERE programID = ?");
$stmt->bind_param("s", $foo);

$foo = "5555";
$stmt->execute();

Upvotes: 0

Related Questions