PHP syntax for postgresql Mixed-case table names

Question

I have a code below:

<?php
require "institution.php"
/* in this portion, query for database connection is executed, and  */

$institution= $_POST['institutionname'];

$sCampID =  'SELECT ins_id FROM institution where ins_name= '$institution' ';
$qcampID = pg_query($sCampID) or die("Error in query: $query." . pg_last_error($connection));
/* this portion outputs the ins_id */           
?>

My database before has no mixed-case table names, that's why when I run this query, it shows no error at all. But because I've changed my database for some reasons, and it contains now mixed-case table names, i have to change the code above into this one:

$sCampID =  'SELECT ins_id FROM "Institution" where ins_name= '$institution' ';

where the Institution has to be double quoted. The query returned parse error. When i removed this portion: where ins_name= '$institution', no error occured.

My question is how do I solve this problem where the table name which contains a mixed-case letter and a value stored in a variable ($institution in this case) will be combined in a single select statement?

Your answers and suggestions will be very much appreciated.

Answer 1

You can use the double quote instead

$sCampID =  "SELECT ins_id FROM \"Institution\" where ins_name= '$institution'";

Answer 2

$sCampID =  'SELECT ins_id FROM "Institution" where ins_name= \''.$institution.'\'';

String escaping.

As another commenter posted, read about SQL injection. What I have is not injection safe, consider using something with prepared statements, preferably PDO.

Answer 3

<?php
require "institution.php"
/* in this portion, query for database connection is executed, and  */

$institution= pg_escape_string($_POST['institutionname']);

$sQuery =  "SELECT ins_id FROM \"Institution\" where ins_name= '$institution'";
$qcampID = pg_query($sQuery) 
  or trigger_error("Error in query: $sQuery." . pg_last_error($connection));
/* this portion outputs the ins_id */           
?>

Note

• pg_escape_string as it ought to be used, not to protect from any injections but as just a part of the syntax.
• trigger_error which should be used instead of echo (and note proper variable name)
• and double quotes or your variable won't be extrapolated ( http://php.net/types.string for ref)
• and slashes at double quotes (same ref)

Answer 4

To add to other answers (quote the table name, and use prepared statements to gain security and performance), read about PG and tables case sensitivity. If you have the option, you might consider to change your db schema, so that tables names (and columns and identifiers in general) are all lowercase. That would simplify a little your queries - (but require you to check all your actual quoted queries and unquote them).