Prepared statement with string interpolation fails with syntax errors

Question

I'm trying to use prepared statements to do a simple insert with PHP and Postgres. So far, I've done this:

<?php
$conn_string = "host=localhost port=5432"; // plus us/pw
$dbconn = pg_connect($conn_string); 

$table  = 'business_primary_category';
$column = 'primary_category';
$tag = 'restuarant';


// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM $table WHERE $column = $1');

// Execute the prepared query.  Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("$tag"));

?>

I've taken it basically from the page on php.net, and can't figure out what I've done wrong. Do I need to install a library to use it or something. Thanks!

These are the errors I get:

Warning: pg_prepare() [function.pg-prepare]: Query failed: ERROR: syntax error at or near "$" at character 15 in /home/url    **......**   pdo.php on line 11

Warning: pg_execute() [function.pg-execute]: Query failed: ERROR: prepared statement "my_query" does not exist in /home/url    **......**   pdo.php on line 15

Warning: pg_execute() [function.pg-execute]: Query failed: ERROR: prepared statement "my_query" does not exist in /home/url    **......**   pdo.php on line 18

You probably need double quotes around your query for your variables to be evaluated. — Mike B
– Mike B, Commented Sep 17, 2012 at 20:22

andrewsi · Accepted Answer · 2012-09-17 20:23:00Z

4

If you want your variables to be interpolated, then you need to use double quotes; else PHP treats it as a string literal. Try:

$result = pg_prepare($dbconn, "my_query", "SELECT * FROM $table WHERE $column = $1");

answered Sep 17, 2012 at 20:23

andrewsi

10.7k132 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

1252748 Over a year ago

hi. thanks! that definitely cleared up the error. neat! I was worried that I had to install some sort of PDO package. Is that for something else?

andrewsi Over a year ago

PDO is a library to access databases - it would replace your existing pg_* functions. PDO is recommended because it makes it easy to write more secure code - it supports prepared statements and bound parameters, which the basic mysql_ functions don't. Since you've already using those, there's no need to re-write everything in PDO for security's sake.

1252748 Over a year ago

Ah, great. so just to be clear. These user suppled strings are definitely sufficiently escaped now that i'm using this prepared statement stuff?

andrewsi Over a year ago

If the only user-based input is in your variables, then yes, you're secure if you pass those in as bound parameters. If you're using user-generated content directly in your SQL, you're not - in your code, if $table or $column are user-generated, you could still have issues, because they're going straight in the query and not being sanitized at all.

1252748 Over a year ago

Oh. how should I sanitize them? Just with a real_escape_string? I've heard that's on the way out. Should I consider this switchover to PDO? is that just for mysql, or does postgre have it as well built into php? Thanks for all the help!

|

ficuscr · Accepted Answer · 2012-09-17 20:22:33Z

2

First thing that jumps out... Use double quotes on 'SELECT * FROM $table WHERE $column = $1'.

answered Sep 17, 2012 at 20:22

ficuscr

7,0932 gold badges35 silver badges54 bronze badges

Collectives™ on Stack Overflow

Prepared statement with string interpolation fails with syntax errors

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related