No query results unless error reporting is turned on

Question

I have a simple query and a simple fetch row:

$quar=mysql_query("SELECT 
                    COALESCE(sum(impression),0) as imps
                   ,COALESCE(sum(clk_count),0) as clks
                   ,COALESCE(sum(money_spent),0) as monspen,time 
                  FROM `$table_name` 
                  WHERE $uid_str $aid_str $timestr time=$temp_time_main 
                  GROUP BY time 
                  ORDER BY time");

$row=mysql_fetch_row($quar);

echo '<pre>';
print_r($row);

The query runs ok, no error, not returning false. Echoed the query and tried in PhpMyAdmin, returning results ok.

Printing $row still returns nothing, tried with fetch assoc, fetch array too. Turned on error_reporting(E_ALL); and voila, print_r returns the array. Disabling error report, returns nothing again. What can be the problem?

print_r() is not a debugging tool, it's not influenced by error_reporting. Are you sure you're not seeing the output of a debugger (xdebug or similar) instead of a regular print_r()? Also, try var_dump() instead, and a die(mysql_error()) after mysql_query(), just in case — Damien Pirsy, Sep 21 '11 at 11:14
As i said, no mysql_errors, and it's working with error_reporting on. var_dump for $quer returns: resource(57) of type (mysql result) and var_dump on $row returns bool(false). — wintercounter, Sep 21 '11 at 11:20
What are the contains of the variables inside the WHERE clause? — Toto, Sep 21 '11 at 11:29
Please specify the contents of the $vars you are injecting into the query when it does not work. This would help a lot. Just add a `echo "var1={$var1} var2={$var2}` into your code and tell the outcome` Don't forget to remove it in production, because that is an XSS. — Johan, Sep 21 '11 at 11:31
No matter, the problem still exists if i try with: SELECT * FROM `$table_name` — wintercounter, Sep 21 '11 at 11:32
No mysql error!!! And the problem still exists with SELECT * FROM table... — wintercounter, Sep 21 '11 at 11:37
Till i don't know any fix, i use this workaround: error_reporting(E_ALL); $res = mysql_query($q); $row=mysql_fetch_row($res); error_reporting(0); Know it's a shity way, but it works for now... — wintercounter, Sep 21 '11 at 12:33

score 2 · Answer 1 · edited May 23 '17 at 12:03

There are a lot of things wrong with this query:

$quar=mysql_query("SELECT 
                    COALESCE(sum(impression),0) as imps
                   ,COALESCE(sum(clk_count),0) as clks
                   ,COALESCE(sum(money_spent),0) as monspen
                   ,time 
                  FROM `$table_name` 
                  WHERE $uid_str $aid_str $timestr time=$temp_time_main 
                  GROUP BY time 
                  ORDER BY time");

1 What does this where-clause mean?

WHERE $uid_str $aid_str $timestr time=$temp_time_main

The where clause is typically of the form: field1 = 'A' AND field2 = 'B' AND field3 > 'C'

2 Inserting unquoted $vars is a bad bad idea

Surround all $vars in single quotes ' if they are values, and in backticks ` if they are column or table names.
That way if the $var contains a space, or other stuff that might trip MySQL your query will not bomb.
You also need to take note that mysql_real_escape_string() will not work if you don't quote your vars.
But using dynamic tablenames renders that point mute, see point 3 below.

3 Dynamic table or column names are a SQL-injection waiting to happen
Use a whitelist to check that the column/table names are valid.
See this question: How to prevent SQL injection with dynamic tablenames?

4 Your COALESCE looks wrong.
Even a single null row will revert your sum to 0. I suggest you use this code instead:

....
SUM(COALESCE(impression,0)) as imps
....

Those variables generating the where statements based on other values, nothing to worry about that or the query, i still have the problem even if i use a simple SELECT * — wintercounter, Sep 21 '11 at 11:25
@wintercounter, it **is** something to worry about because it is a security hole big enough to run a freeway through. — Johan, Sep 21 '11 at 11:29
Here it is: where `uid`='10' <- the variable returns this, the other two is empty in this case. Now you can see why i said it's no matter, i'm using quotes in my queries... — wintercounter, Sep 21 '11 at 11:36
OK and what will happen if I put: `now() GROUP BY time ORDER BY time UNION select username, password, email, null from users` into `$temp_time_main`? — Johan, Sep 21 '11 at 11:44
That still has '' in the query, just removed when debugging, and u can't, it's a hardcoded parameter. I have experiences in SQL Injections, no need advices for that. — wintercounter, Sep 21 '11 at 11:50

score 0 · Accepted Answer · answered Sep 21 '11 at 11:19

0

May be this will help to debug the problem:

$query = "select COALESCE(sum(impression),0) as imps,COALESCE(sum(clk_count),0) as clks,COALESCE(sum(money_spent),0) as monspen,time from `$table_name` where $uid_str $aid_str $timestr time=$temp_time_main group by time order by time";

$result = mysql_query($query) || die('Error: ' . mysql_error());

while (($row=mysql_fetch_row($result)) !== false) {
  var_dump($row);
}

answered Sep 21 '11 at 11:19

classic

544
2
7

No mysql errors as i said. Look at my comment on my original post. – wintercounter Sep 21 '11 at 11:24
Ok. Then try to setup ini_set('display_errors',1); and after launching script in browser look at the source HTML code. Are there any errors? – classic Sep 21 '11 at 12:30
No mysql errors (saying this 20 times it's annoying now, sry). This works:error_reporting(E_ALL);$res = mysql_query($q);$row=mysql_fetch_row($res);error_reporting(0); It's a shity way, but it's working for now till i find a fix. – wintercounter Sep 21 '11 at 12:43
I read the question and didn't consider the case when your SQL contains error as you wrote you checked it in phpMyAdmin. Just tried to help you find out problem in your script (not SQL). – classic Sep 21 '11 at 13:29

No query results unless error reporting is turned on

2 Answers2