Does pg_prepare() prepared statement (not PDO) pre

Question

PDO ist not supported in target system I'm working on and though I seek a solution for preventing SQL-Injection using PHP 5.1.x on a PostGres-DB 8.2+. There is at the moment no chance of switching to PDO.

My solution at the moment is pg_prepare-prepared statement:

// Trying to prevent SQL-Injection
$query = 'SELECT * FROM user WHERE login=$1 and password=md5($2)';
$result = pg_prepare($dbconn, "", $query);
$result = pg_execute($dbconn, "", array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
  die ("failure");
}

But pg_prepare-documentation lacks about an important information:

it tells about "later usage"

pg_prepare() creates a prepared statement for later execution with pg_execute() or pg_send_execute().[...]

it tells about "named/anonymous statements"

The function creates a prepared statement named stmtname from the query string, which must contain a single SQL command. stmtname may be "" to create an unnamed statement, in which case any pre-existing unnamed statement is automatically replaced;[...]

it tells about "typecasting"

Prepared statements for use with pg_prepare() can also be created by executing SQL PREPARE statements. (But pg_prepare() is more flexible since it does not require parameter types to be pre-specified.) Also, although there is no PHP function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.

but it does not tell, if this implementation of prepared statements is safe from SQL-injection

*Nearly all comments by this security question refers to the PDO-solution, where in documentation is noticed that the driver prevents SQL-injection. But if an easy solution may be pg_prepare, I would use pg_prepare at the moment.*

Thanks for this important information of maybe a best practice solution.

EDIT (after marked as solution): Thanks for very enlightening answers!

• I marked the solution of Frank Heikens as best answer, cause it explains an important point in SQL-injection. A programmer may use prepared statemtents, but the SQL-injection-lack may still be there by mistake!
• Aside from Frank Heikens answer, hoppa shows that the SQL-injection is prevented using pg_prepare/pg_query_params. Thanks though.
• Will now use an optimized code with pg_query_params (thanks to Milen A. Radev)
• And pg_escape_string() as alternative when it comes to it (thanks to halfer)

All answers are helpfully :)

// Trying to prevent SQL-Injection (**updated**)
$sql_query = 'SELECT * FROM user WHERE login=$1 and password=md5($2);';
$result = pg_query_params($dbconn_login, $sql_query, array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
  die('failure');
}

Answer 1

As far as I could gather from the docs it should guard you against SQL injection.

A more generic approach would be to use pg_query_params as it's not connected with preparing the query.

Answer 2

A prepared statement is safe from SQL injection because nobody can change the queryplan after it's prepared. But, if your statement is already compromised, you still suffer from SQL injection:

<?php 
// how NOT to construct your SQL....
$query = 'SELECT * FROM user WHERE login=$1 and password=md5($2) LIMIT '. $_POST['limit']; -- injection!
$result = pg_prepare($dbconn, "", $query);
$result = pg_execute($dbconn, "", array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
  die ("failure");
}
?>

Answer 3

Using prepared statements is generally the best way to go, since you should also get better SQL performance from database optimisations that can be skipped.

However it is always good to know alternative ways of doing things, so bear in mind you can use pg_escape_string() on your tainted variables and then use the outputs directly in a SQL query.

Answer 4

Prepared statements are built into MySQL (http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html). The injection prevention mechanism is also in MySQL, see this quote from the previously linked page:

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

PHP libraries are just mapping their functionality to MySQL functions (probably using http://docs.oracle.com/cd/E17952_01/refman-5.0-en/c-api-prepared-statement-function-overview.html). So yes, pg_prepare should also safeguard you for injection.

[EDIT] I just noticed you're talking about PostgreSQL, for PostgreSQL the same goes, it is a built in language feature, not something a PHP library provides.