Strange behavior with SELECT WHERE A="$var" SQL

Question

I looked for an answer but could not find it as the issue seems to be slightly different here.

$vid = $_SESSION['ID_Vendor'];
echo "ID: $vid";
$q = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=res.ID_Vendor AND res.ID_Vendor="$vid" ORDER BY business_name ASC';

The variable $vid has indeed a value (equal to 2 in this case, but it could be different), HOWEVER, when I specifically set

 WHERE res.ID_Vendor=2

my query returns the correct and expected list of values, but when, instead, I use

 WHERE res.ID_Vendor="$vid"

with "$vid", the echo of my values is simply empty.

Below is the full snippet of the code to also echo the output. Thanks for your help.

 $vid = $_SESSION['ID_Vendor'];
 echo "ID: $vid";
 $q = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=sfe.ID_Vendor AND res.ID_Vendor="$vid" ORDER BY   business_name ASC';
 $r = mysqli_query($connection, $q);
 while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {

  echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

Does this make any difference? `$q = "SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment FROM restaurant res INNER JOIN vendor_data vd ON vd.ID_Vendor=res.ID_Vendor AND res.ID_Vendor=$vid ORDER BY business_name ASC";` — blazerunner44, Mar 10 '16 at 05:17
Echo your query before you execute it and I think the problem will make itself obvious. You could avoid the problem entirely if you read up on prepared statements. — Darwin von Corax, Mar 10 '16 at 05:18
Darwin, thanks. But, why would a prepared statement make a difference? I would think that a stored procedure would work instead, isn't that right? Let me try both. — s coronado, Mar 10 '16 at 05:30
Among other things, prepared statements handle the quoting and type-matching of query parameters automatically for you. — Darwin von Corax, Mar 10 '16 at 05:52
A simple **`echo $q;`** would have revealed one of the problems. The observed behavior is not "strange". It's well defined and well documented. — spencer7593, Mar 10 '16 at 05:53

score 2 · Answer 1 · answered Mar 10 '16 at 05:28

2

PHP does not recognize variable in apostrophes pair ''. Put your variable in "". EG:

"WHERE res.ID_Vendor='$vid'"

answered Mar 10 '16 at 05:28

ThangTD

1,586
17
16

Thank you @ThangTD and Luweiq, your solution solved the problemI still want to try prepared statements as proposed by Darwin as they even improve security. – s coronado Mar 10 '16 at 05:50

score 1 · Accepted Answer · answered Mar 10 '16 at 22:46

Since I raised the idea, I suppose I should illustrate the use of prepared statements.

Using mysqli one would proceed as follows (assuming $connection has been successfully initialized):

// The indentation here is purely a matter of personal preference
$query = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
            FROM restaurant res
            INNER JOIN vendor_data vd
              ON vd.ID_Vendor = res.ID_Vendor
            WHERE res.ID_Vendor = ?
            ORDER BY business_name ASC';

$stmt = $connection->prepare($query);
$stmt->bind_param('s', $vid);  // 's' assumes $vid is string; use 'i' for int
$stmt->execute();
$res = $stmt->get_result();
while ($row = $res->fetch_array(MYSQLI_NUM))
{
    echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

The idiom is the same using PDO. The format of the PDO data source name (DSN) is documented online.

$conn = new PDO($dsn, $username, $password); // define these vars elsewhere
$query = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
            FROM restaurant res
            INNER JOIN vendor_data vd
              ON vd.ID_Vendor = res.ID_Vendor
            WHERE res.ID_Vendor = :vid
            ORDER BY business_name ASC';
$stmt = $conn->prepare($query);
$stmt->execute(array(':vid' => $vid));
while ($row = $stmt->fetch(PDO::FETCH_NUM))
{
    echo '>' . htmlspecialchars($row[0]) . '  ' . htmlspecialchars($row[1]) . '   ' . htmlspecialchars($row[2]) .'</option>';
}

In both cases I leave error handling as an exercise for the reader.

score 0 · Answer 3 · answered Mar 10 '16 at 05:33

There's a few problems with your code:

Firstly, this won't work and will echo $vid literally.

echo "ID: $vid";

This will return: ID: $vid

You'll need to concat the string with the variable, it should be:

echo "ID: " . $vid;

This will return: ID: 2

Secondly, your query won't work:

$q = 'SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=res.ID_Vendor AND res.ID_Vendor="$vid" ORDER BY business_name ASC';

You should enclose the query with double quotes and your variable with single quotes instead:

$q = "SELECT business_name, vd.ID_Vendor, res.ID_RestaurantEstablishment 
  FROM restaurant res
   INNER JOIN vendor_data vd
  ON vd.ID_Vendor=res.ID_Vendor AND res.ID_Vendor='$vid' ORDER BY business_name ASC";

score 0 · Answer 4 · answered Mar 10 '16 at 05:48

For SQL, not like javascript or php, only single quote is allowed for string or char.

For MySQL, if the column type of res.id_vendor is number, res.id_vendor='1' is the same as res.id_vendor=1, so it is better always wrap the variable with single quote, as in res.id_vendor='$vid'

Lastly, comments on coding conventions: it is recommended to not mix upper case and lower case characters in column naming, use "_" as word delimiters, like

vd.id_vendor
res.id_restaurant_establishment

Strange behavior with SELECT WHERE A="$var" SQL

4 Answers4

Linked