PHP syntax for postgresql Mixed-case table names

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.

---

You can use the double quote instead

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

---

<?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)

---

$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.

---

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).

---

What happens if $institution contains the following string: ' or 1 = 1; --

That's what we call an SQL injection attack, and it's a super-easy way for hackers to steal your data -- and get you into big trouble with your customers.

You need to escape that string using pg_escape_string() before putting it into an SQL query. I like to use sprintf() to build my queries:

$sql = sprintf("SELECT ins_id FROM \"Institution\" where ins_name= '%s'", pg_escape_string($conn, $institution));

In the above example, $conn is the connection identifier, created by calling pg_connect().