getting problem in pg_query execution

Question

When i am running my query

$dbhost='localhost';
$dbuser='dbuser';
$dbpass='dbpass';
$dbname='dbname';

$conn=pg_connect('host='.$dbhost.' dbname='.$dbname.' user='.$dbuser.' password='.$dbpass); 
if (!$conn) {
  echo "An error occured.\n";
  exit;
}else{
echo "connection occured";
}
echo $conn;


        $insert=pg_query($conn, "Insert into `advertiser_regidetdir` 
        (user_name,domain_name,user_email,publickey,privatekey, refresh_count )
        values('$user','$domain','$email','$pubkey','$pritekey',0)");
        if (!$insert) {
          echo "An error occured.\n";
          exit;
        }

I am getting output somthing like this....... connection occured Resource id #2An error occured. Why this error is coming.

Answer 1

Use the pg_last_error() function to get a more detailed error message like this:

    if (!$insert) {
      echo "An error occured.\n";
      echo pg_last_error($conn);
      exit;
    }

Answer 2

Insert into `advertiser_regidetdir`

Backticks are a MySQL quoting feature that is not part of standard ANSI SQL and not available in other databases. The ANSI syntax for quoting a schema name is double quotes.

It is unfortunate that MySQL interprets double quotes as a synonym for single quotes. If you want to write code that is portable across MySQL and other DBMSs, you'll need to either:

• use double-quotes for schema names throughout, and on MySQL turn on the sql_mode ANSI_QUOTES feature in order to make it comply with the standard, or
• avoid quoting schema names altogether. This requires general knowledge of what keywords exist in different DBMSs so you can avoid those words, but certainly advertiser_regidetdir is not a keyword in any DBMS so is quite valid without the quotes.

.

values('$user','$domain','$email','$pubkey','$pritekey',0)

Unless you have already pg_escape_stringed those values, that's a bit old load of SQL injection security horror.

Consider parameterised queries, especially as with the pg module you get pg_query_params which makes it really easy.

Answer 3

Use pg_query_params() to avoid SQL injection and don't use backticks `, these have nothing to do with SQL:

<?php
$dbhost='localhost';
$dbuser='dbuser';
$dbpass='dbpass';
$dbname='dbname';

$conn = pg_connect('host='.$dbhost.' dbname='.$dbname.' user='.$dbuser.' password='.$dbpass); 

if (!$conn) {
  echo "An error occured.\n";
  exit;
}else{
echo "connection occured";
}
echo $conn;

$query = '
    INSERT INTO advertiser_regidetdir
        (user_name,domain_name,user_email,publickey,privatekey, refresh_count )
    VALUES($1, $2, $3, $4, $5, 0)'; // 5 placeholders

$insert = pg_query_params(
            $conn,
            $query,
            array($user, $domain, $email, $pubkey, $pritekey) // array with values
            );

if (!$insert) {
    echo "An error occured.\n";
    exit;
}
?>