PHP query returning only one row out of four

Question

This query was supposed to return me four rows: which are four people with status 50 (which, in the application means "maternity leave"). But it returns only one.

On HeidiSQL the query doesn't even run because it displays a

syntax error on line 13:

(...)

corresponds to your MariaDB server version for the right syntax to use near 'a.id_regiao = '$id_regiao' AND a.cod_status = 50 AND a.status' at line 13 */"

Here is the query. I'm slowly becoming familiar with sql statements and i did search a lot on SO before asking it:

//SELECTING PROJECT DATA
$query = "SELECT b.id_clt,b.nome AS nome_clt,
        a.id_evento AS a_id_evento,a.data AS a_data,a.data_retorno AS a_data_retorno,
        c.id_evento AS c_id_evento,c.data AS c_data,c.data_retorno AS c_data_retorno,
        (SELECT nome FROM projeto WHERE id_projeto = a.id_projeto) AS nome_projeto,
        (SELECT nome FROM curso WHERE id_curso = b.id_curso) AS nome_curso,
        DATE_FORMAT(a.data,'%d/%m/%Y') AS a_data_br,
        DATE_FORMAT(a.data_retorno,'%d/%m/%Y') AS a_data_retorno_br,
        DATE_FORMAT(c.data,'%d/%m/%Y') AS c_data_br,
        DATE_FORMAT(c.data_retorno,'%d/%m/%Y') AS c_data_retorno_br
        FROM rh_eventos AS a
        INNER JOIN rh_clt AS b ON (a.id_clt = b.id_clt AND a.cod_status = 50)
        LEFT JOIN rh_eventos AS c ON (b.id_clt = c.id_clt AND c.cod_status = 54)
        WHERE $cond_projeto a.id_regiao = '$id_regiao' 
        AND a.cod_status = 50
        AND a.status = 1 
        AND NOW() BETWEEN a.data AND a.data_retorno 
        ORDER BY nome_projeto,b.nome;";

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jan 11 '19 at 13:33

score 3 · Answer 1 · answered Jan 11 '19 at 13:04

3

The problem is here in the query:

    WHERE $cond_projeto a.id_regiao = '$id_regiao'

This inserts a variable (or maybe a full test?) without proper syntax. If it is a variable, include the table's column name in the criterium. If it is a full test, include AND like so:

    WHERE $cond_projeto AND a.id_regiao = '$id_regiao'

Beware though! Use prepared statements, your code now appears to be vulnerable to SQL injection attacks (and those are not to be trifled with).

answered Jan 11 '19 at 13:04

T. Altena

752
4
15

Props for *"those are not to be trifled with"* – Jay Blanchard Jan 11 '19 at 13:34
1

I was trying to think of the name of the Little Bobby site, thanks for adding that in :-) – T. Altena Jan 11 '19 at 13:38
@user10847903: The problem is with the other parameter being `$cond_projeto`, which does not fit in with the syntax requirements. Read up on Prepared Statements - especially if you're in the early days of building the SQL interactions, now is the time to switch over and be sensible about including variables in the SQL syntax. https://www.w3schools.com/php/php_mysql_prepared_statements.asp – T. Altena Jan 11 '19 at 13:49
'$id_regiao' stands for the region id in the database, wich in the case of a "maternity leave status", the region is = 1. WHERE $cond_projeto a.id_regiao = 1 on heidiSQL, replacing the variable for the status (1) it keeps on returning nothing. I'm not sure if i understood your explanation. – user10847903 Jan 11 '19 at 13:50
Can you explain which values $cond_projeto could have? – T. Altena Jan 11 '19 at 14:13

score 0 · Answer 2 · answered Jan 11 '19 at 15:51

Here is the query, (as seen by using an echo before it) . I can see the output on heidsql now. Now its better for us to check it:

SELECT b.id_clt,b.nome AS nome_clt, 
a.id_evento AS a_id_evento,a.data AS a_data,a.data_retorno AS a_data_retorno,
 c.id_evento AS c_id_evento,c.data AS c_data,c.data_retorno AS c_data_retorno,
  (SELECT nome FROM projeto WHERE id_projeto = a.id_projeto) AS nome_projeto, 
  (SELECT nome FROM curso WHERE id_curso = b.id_curso) AS nome_curso, 
  DATE_FORMAT(a.data,'%d/%m/%Y') AS a_data_br, 
  DATE_FORMAT(a.data_retorno,'%d/%m/%Y') AS a_data_retorno_br,
   DATE_FORMAT(c.data,'%d/%m/%Y') AS c_data_br, 
    DATE_FORMAT(c.data_retorno,'%d/%m/%Y') AS c_data_retorno_br 
    FROM rh_eventos AS a 
    INNER JOIN rh_clt AS b ON (a.id_clt = b.id_clt AND a.cod_status = 50) 
    LEFT JOIN rh_eventos AS c ON (b.id_clt = c.id_clt AND c.cod_status = 54) 
    WHERE a.id_regiao = '1' AND a.cod_status = 50
    AND a.status = 1 
    AND NOW() BETWEEN a.data AND a.data_retorno ORDER BY nome_projeto,b.nome;

I can now see the output on heidsql, though i still cant figure out why it doesent bring the other thre rows.

PHP query returning only one row out of four

2 Answers2