0

I want my application to dynamically change which database and schema name it uses depending on whether it's running on a local machine, staging environment, or production. I thought pg_query_params would be a great choice because you can dynamically substitute things into your queries by parameterizing them. The idea is the schema name will be dynamically inserted into the query. But, pg_query_params() doesn't seem to be substituting the values in the array, causing a query syntax error.

I have verified that the query itself is valid by using pg_query and hardcoding the schema name in versus parameterizing it. When I look at the logs, I see the following error:

PHP Warning: pg_query_params(): Query failed: ERROR: syntax error at or near "$1. Line 2 FROM $1.module__c

So, clearly, pg_query_params isn't actually substituting the parameters in (at least from what I can see)

As a result, pg_query_params() returns false, and breaks the whole page.

The code first gets an array from my databaseInfo() function (it returns an array for the schema name and connection resource. I've verified that this part of my code is working and returning the expected information. 

$database = databaseInfo();
if (isset($projSFID)) {
    $placeholder = $projSFID;
    $query = "SELECT $1.module__c.name, $1.module__c.module_title__c, $1.module__c.number_of_units__c, $1.module__c.duration_minutes__c, $1.module__c.module_bg_hex__c, $1.module__c.module_description__c, $1.lms_asset__c.lms_asset_url__c
                FROM $1.module__c
                INNER JOIN $1.teproject__c ON $1.module__c.associated_project__c = $1.teproject__c.sfid
                INNER JOIN $1.lms_asset__c ON $1.module__c.module_image_url__c = $1.lms_asset__c.sfid
                WHERE $1.teproject__c.sfid = $2 AND $1.module__c.published__c=TRUE";
} elseif (isset($trackSFID)) {
    $placeholder = $trackSFID;
    $query = "SELECT $1.module__c.name, $1.module__c.module_title__c, $1.module__c.number_of_units__c, $1.module__c.duration_minutes__c, $1.module__c.module_bg_hex__c, $1.module__c.module_description__c, $1.lms_asset__c.lms_asset_url__c
                FROM $1.module_track_association__c
                    INNER JOIN $1.module__c ON $1.module_track_association__c.module__c = $1.module__c.sfid
                    INNER JOIN $1.track__c ON $1.module_track_association__c.track__c = $1.track__c.sfid
                    INNER JOIN $1.lms_asset__c ON $1.module__c.module_image_url__c = $1.lms_asset__c.sfid
                WHERE $1.track__c.sfid = $2
                ORDER BY $1.module_track_association__c.navigation_sequence__c";
} else {
    $placeholder = '';
    $query = "SELECT $1.module__c.name, $1.module__c.module_title__c, $1.module__c.number_of_units__c, $1.module__c.duration_minutes__c, $1.module__c.module_bg_hex__c, $1.module__c.module_description__c, $1.lms_asset__c.lms_asset_url__c
                FROM $1.module__c
                INNER JOIN $1.lms_asset__c ON $1.module__c.module_image_url__c = $1.lms_asset__c.sfid
                WHERE $1.module__c.published__c=TRUE AND $1.module__c.display_on_frontend_filtered_only__c=FALSE $2";
    echo $query;
}

$result = pg_query_params($database['connection'], $query, array($database['schema'],$placeholder));

The expected result is that $1 will be the name of the schema and $2 will be the value of the placeholder as determined by the conditional logic.

Patrick Vogt
  • 1
  • 2
  • You can't use parameters for SQL syntax. Placeholders are only used to separate the data. – Dharman Jan 11 '19 at 20:12
  • You specify the schema name in `pg_connect`. You then save the connection details in an environment file outside of your web directory. – Dharman Jan 11 '19 at 20:16
  • It wasn't clear looking at the documentation that you couldn't use parameters in SQL syntax (http://php.net/manual/en/function.pg-query-params.php). I assumed you could because in the example it was written: $result = pg_query_params($dbconn, 'SELECT * FROM shops WHERE name = $1', array("Joe's Widgets")); – Patrick Vogt Jan 11 '19 at 20:24
  • In the PHP manual it is only used in the place of data. The parameter's purpose is separate SQL command from the data you supply to it. Check out examples at http://bobby-tables.com/ – Dharman Jan 11 '19 at 20:26
  • If you go to the linked page and look at example 1, it is using $1 after the equals sign. I assume that's what you mean when you say it's being used in place of the data. So, PHP doesn't just plug your value in wherever you put the parameter, only after an equals sign... am I understanding that correctly? – Patrick Vogt Jan 11 '19 at 20:29
  • The data will be send separate from the SQL command to DB. [How can I explain SQL injection without technical jargon?](https://security.stackexchange.com/a/25710/188415) – Dharman Jan 11 '19 at 20:32

0 Answers0