PHP and postgres SQL with placeholders?-CodePudding

I have read a lot of PHP sql examples that use MySQL. I often see queries like this that have placeholders for values:


$query = “INSERT INTO users
(username, firstname, lastname, salutation, countrycode)
VALUES(:1, :2, :3, :4, :5)”;

Notice the values are :1, :2 etc.

I use the standard PHP module for postgres in PHP. My queries inject variables names like this:


$query = “INSERT INTO users(username)
VALUES($hello)”;

How do I use the same placebolder technique with postgres in PHP? Is it possible?

CodePudding user response：

Prepared queries cause two database communication roundtrips instead of one: one for prepare, one for execute. If you want to reuse the same query many times, yet only parse it once, they will be faster, but if you want to use the query once, they will be slower. Basically, prepared queries are the wrong answer to your problem ; the correct answer is parameterized queries.

$result = pg_query_params($dbconn, 
          'SELECT * FROM users WHERE first_name = $1 AND last_name=$2', 
          array($firstname,$lastname));

This pg-specific function makes a parameterized query where placeholders of the form $number are replaced by the parameters with the corresponding one-based index in the array. Parameters are properly handled, and there is no risk of SQL injection.