Using PHP variable in SQL query

Question

I'm having some trouble using a variable declared in PHP with an SQL query. I have used the resources at How to include a PHP variable inside a MySQL insert statement but have had no luck with them. I realize this is prone to SQL injection and if someone wants to show me how to protect against that, I will gladly implement that. (I think by using mysql_real_escape_string but that may be deprecated?)

<?php
$q = 'Hospital_Name';
$query = "SELECT * FROM database.table WHERE field_name = 'hospital_name' AND value = '$q'";

$query_result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($query_result)) {
   echo $row['value'];
}
?>

I have tried switching '$q' with $q and that doesn't work. If I substitute the hospital name directly into the query, the SQL query and PHP output code works so I know that's not the problem unless for some reason it uses different logic with a variable when connecting to the database and executing the query.

Thank you in advance.

Edit: I'll go ahead and post more of my actual code instead of just the problem areas since unfortunately none of the answers provided have worked. I am trying to print out a "Case ID" that is the primary key tied to a patient. I am using a REDCap clinical database and their table structure is a little different than normal relational databases. My code is as follows:

<?php
$q = 'Hospital_Name';
$query = "SELECT * FROM database.table WHERE field_name = 'case_id' AND record in (SELECT distinct record FROM database.table WHERE field_name = 'hospital_name' AND value = '$q')";

$query_result = mysqli_query($conn, $query);
while ($row = mysqli_fetch_assoc($query_result)) {
   echo $row['value'];
}
?>

I have tried substituting $q with '$q' and '".$q."' and none of those print out the case_id that I need. I also tried using the mysqli_stmt_* functions but they printed nothing but blank as well. Our server uses PHP version 5.3.3 if that is helpful.

Thanks again.

you have a stray bracket in this `'$q')";` so where is `(` ? Edit: as per https://stackoverflow.com/revisions/44396690/1 — Funk Forty Niner, Jun 06 '17 at 17:55
If I type in the name of the hospital, it outputs what I want, and if I use a variable, it outputs nothing. — Christopher Sinclair, Jun 06 '17 at 18:02
*"because when I substitute in the hospital name it works"* - which is what *exactly?* and I mean... "exactly". As in `St-Mary` or `St-mary` or `Saint Mary's`? it matters you know. — Funk Forty Niner, Jun 06 '17 at 18:03
`WHERE field_name = 'hospital_name' AND value = '$q'` both conditions must be met and perfectly. My above comment was edited btw. You may have to include real values from your db. — Funk Forty Niner, Jun 06 '17 at 18:05

clearshot66 · Answer 1 · 2017-06-06T17:58:56.557

Do it like so

<?php
$q = 'mercy_west';
$query = "SELECT col1,col2,col3,col4 FROM database.table WHERE field_name = 'hospital_name' AND value = ?";
if($stmt = $db->query($query)){
  $stmt->bind_param("s",$q);   // s is for string, i for integer, number of these must match your ? marks in query. Then variable you're binding is the $q, Must match number of ? as well
  $stmt->execute();
  $stmt->bind_result($col1,$col2,$col3,$col4);  // Can initialize these above with $col1 = "", but these bind what you're selecting. If you select 5 times, must have 5 variables, and they go in in order. select id,name, bind_result($id,name)
  $stmt->store_result();
  while($stmt->fetch()){   // fetch the results
    echo $col1;
  }
  $stmt->close();
}

?>

score 1 · Answer 2 · answered Jun 06 '17 at 21:24

Yes mysql_real_escape_string() is deprecated.

One solution, as hinted by answers like this one in that post you included a link to, is to use prepared statements. MySQLi and PDO both support binding parameters with prepared statements.

To continue using the mysqli_* functions, use:

mysqli_prepare() to get a prepared statement
mysqli_stmt_bind_param() to bind the parameter (e.g. for the WHERE condition value='$q')
mysqli_stmt_execute() to execute the statement

mysqli_stmt_bind_result() to send the output to a variable.

<?php
$q = 'Hospital_Name';
$query = "SELECT value FROM database.table WHERE field_name = 'hospital_name' AND value = ?";

$statement = mysqli_prepare($conn, $query);

//Bind parameter for $q; substituted for first ? in $query
//first parameter: 's' -> string
mysqli_stmt_bind_param($statement, 's', $q); 

//execute the statement
mysqli_stmt_execute($statement);

//bind an output variable
mysqli_stmt_bind_result($stmt, $value);

while ( mysqli_stmt_fetch($stmt)) {
    echo $value; //print the value from each returned row
}

If you consider using PDO, look at bindparam(). You will need to determine the parameters for the PDO constructor but then can use it to get prepared statements with the prepare() method.

Using PHP variable in SQL query

2 Answers2