Warning: pg_query(): Query failed: ERROR: syntax error at or near

Question

I have a script that is supposed to display the results of a Postgres query based on a link that the user clicks on a previous page.

For example, when the user clicks on the Title of a project, it directs them to a page that shows them more attributes about the project, which are contained in columns in the database.

I already have the page working where users can click on a Title, but once they click a title, my second page to display the other attributes of the project is not working.

<?php

ini_set('display_errors',1);  error_reporting(E_ALL);

$row = false;

if (isset($_GET['pid']) && filter_var($_GET['pid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) { 

    $pid = $_GET['pid'];

    require('/var/www/postgres_connect.php');
    $q = 'SELECT * FROM public.' + "tblProjects" + 'WHERE ' + "tblProjects" + '.ProjectID = ' + "$pid";
    $r = pg_query ($dbconn, $q);
    if (pg_num_rows($r) == 1) {

        $row = pg_fetch_assoc ($r);

        $page_title = $row['ProjectID'];

        echo "<div align=\"center\">
        <b>{$row['ProjectID']}</b> by
        {$row['ProjectTitle']}<br />";

        echo '<p align="center">' . ((is_null($row['totalcost'])) ? '(No Cost Recorded)' :
        $row['totalcost']) . '</p>';

    }   
    pg_close($dbconn);
}

if (!$row) {
    $page_title = 'Error';
    echo '<div align="center">This page encountered an error!</div>';
    }


?>

Running this script produces the following error:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "20131418" LINE 1: 20131418 ^ in /var/www/html/view_project.php on line 13

Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in /var/www/html/view_project.php on line 14

Now, I don't think the second error is a problem because solving the first error will produce a result of the query and then clear up the second error.

I don't understand what is wrong with the syntax; having $pid at the end of the query returns an integer(20131418) which is being called out as invalid syntax. What can I do to solve this issue?

Concatenation is done with the dot operator `.` in PHP, not the `+` operator, which is only for numeric addition (and non-numeric strings are cast to zero before "adding" them) — Michael Berkowski, Dec 21 '14 at 22:28
So, this line doesn't do anything like it's supposed to: `$q = 'SELECT * FROM public.' + "tblProjects" + 'WHERE ' + "tblProjects" + '.ProjectID = ' + "$pid";` — Michael Berkowski, Dec 21 '14 at 22:29
If you did: `echo "SELECT * FROM " + tblProjects`, php will convert 2 non-numeric strings to zero, resulting in `0 + 0 = 0` The `$pid`, if numeric, will add some number to that, so the entire query `$q` results in single number rather than a SQL string. — Michael Berkowski, Dec 21 '14 at 22:31
Ok - that makes sense. But, if I try to put the entire query into quotes, I can't get it to work either. For starters, I know that this query works: `'SELECT * FROM public."tblProjects"'` However, single quotes do not allow me to reference the pid value within the query, so I can't figure out how to formulate the query. — ge0m3try, Dec 21 '14 at 23:12
Gah. `pg_query_params` please. Always. Even if you think you're only working with numbers. See http://php.net/manual/en/security.database.sql-injection.php — Craig Ringer, Dec 22 '14 at 02:08

score 2 · Accepted Answer · edited May 23 '17 at 12:12

The correct syntax for the SQL statement is as follows:

'SELECT * FROM public."tblProjects" WHERE "tblProjects"."ProjectID"=' . $pid;

The main reason for this not working in the first place was because of mixed cases in column and table names.

See this answer to a similar question: https://stackoverflow.com/a/12250721/3620249

I had thought the $pid needed to be within the query string (single quotes). However, the variable would not be called unless the query string was double quoted. It then became difficult to manage the mixed cases in the column/table names with quotes as well, so I tried using + to concatenate instead of . as you can see in my question.

Lessons learned:

Concatenate in PHP using .
Variables can be called from outside of a query string using concatenation.
If table/column names contain mixed cases, they must be contained within double quotations

Warning: pg_query(): Query failed: ERROR: syntax error at or near

1 Answers1

Linked