Query using column named "index" using PDO and MSSQL database always returns nothing

Question

I have a database with 2 tables called data.MonthlyNormalData and dev.Station in which I want to use a query with an INNER JOIN. In that query, specifically in the WHERE clauses, I use a condition on a column named index from the data.MonthlyNormalData table. Of course, in sql server, index is a reserved word and I can escape it by writing the following: WHERE [index] = 'foo'.

Now my issue is that when doing my query in my PHP script using PDO, I don't receive any data, but in sql server, with the same query, I do see data returning from the query. I think that that is because in sql server I escaped the index keyword whereas in my script I did not. Is this really what is going on? If so, how can I resolve this problem?

For reference, here is the query in my PHP script:

$db = ConnectionFactory::getFactory()->getConnection();
$stmt = $db->prepare("SELECT MND.value, MND.normal_code FROM data.MonthlyNormalData AS MND INNER JOIN dev.Station AS S ON MND.station_id = S.station_id WHERE MND.element_id = $indice[1][0] AND MND.index = $indice[1][1] AND MND.threshold = $indice[1][2] AND MND.month = $monthNum AND S.station_id = $slt_idStations ORDER BY MND.last_updated DESC");
$stmt->execute();
if ($row = $stmt->fetch())
{
    array_push($queryResults, $row['value']);
}
else
{
    echo "NO DATA :'( !!!!! </br>";
}

Where $indices[1][x], $monthNum and $slt_idStations are integers. I have checked by echo'ing them that they have the right values, and they indeed do. The ConnectionFactory class sets up all the connection configuration and options for the PDO connection and I used it all throughout my code without any problems.

Here is the query I used on sql server and it worked (I replaced the PHP variables in the query by their actual values):

SELECT MND.value, MND.normal_code 
FROM [criacc_bd].[data].[MonthlyNormalData] AS MND 
INNER JOIN [criacc_bd].[dev].[Station] AS S 
ON MND.station_id = S.station_id 
WHERE MND.element_id = '54' 
AND MND.[index] = '0' 
AND MND.threshold = '0' 
AND MND.month = '12' 
AND S.station_id = '6047' 
ORDER BY MND.last_updated DESC

EDIT: Just got confirmation that the index column is the issue, as when I remove the AND MND.index = $indice[1][1] part of my PHP query, it does return values.

Why are you preparing a statement when you are injecting your variables directly in the query? You should bind your variables instead to avoid sql injection. — jeroen, Aug 20 '14 at 16:43

score 1 · Answer 1 · answered Aug 20 '14 at 16:49

Your formatted query:

SELECT MND.value, MND.normal_code
FROM data.MonthlyNormalData AS MND INNER JOIN dev.Station AS S
  ON MND.station_id = S.station_id
WHERE
  MND.element_id = $indice[1][0] AND
  MND.index = $indice[1][1] AND
  MND.threshold = $indice[1][2] AND
  MND.month = $monthNum AND
  S.station_id = $slt_idStations
ORDER BY MND.last_updated DESC

You cannot expand array values inside of a quoted string like that, it needs to be:

SELECT MND.value, MND.normal_code
FROM data.MonthlyNormalData AS MND INNER JOIN dev.Station AS S
  ON MND.station_id = S.station_id
WHERE
  MND.element_id = {$indice[1][0]} AND
  MND.index = {$indice[1][1]} AND
  MND.threshold = {$indice[1][2]} AND
  MND.month = $monthNum AND
  S.station_id = $slt_idStations
ORDER BY MND.last_updated DESC

Or better yet, use parameters since you're clearly already using an engine that supports prepared statements.

I have tried your suggestion, but it unfortunately is not the solution. The problem really lies with the index column, as when I remove the `AND MND.index = $indice[1][1]` part of my query in my PHP script, the query works perfectly (but doesn't return the exact data I want as there is a part missing in the query now). I will update my original question with that info. — Choub890, Aug 20 '14 at 16:57

score 0 · Accepted Answer · edited May 23 '17 at 11:49

The problem was a mix of the index column and what Sammitch mentioned in his answer. The fix was to take Sammitch's answer and also escape the index with [] in the prepare() function like follows:

SELECT MND.value, MND.normal_code 
FROM data.MonthlyNormalData AS MND 
INNER JOIN dev.Station AS S 
ON MND.station_id = S.station_id 
WHERE MND.element_id = {$indice[1][0]} 
AND MND.[index] = {$indice[1][1]} 
AND MND.threshold = {$indice[1][2]} 
AND MND.month = $monthNum 
AND S.station_id = $slt_idStations 
ORDER BY MND.last_updated DESC

I found out about the bracket escaping by reading the following answer: Unable to quote table name in PDO with MSSQL

Query using column named "index" using PDO and MSSQL database always returns nothing

2 Answers2