0

PHP 7.0

Server version: 10.0.34-MariaDB-0ubuntu0.16.04.1

This is probably the strangest issue I ever encountered.

My goal is to sum the values from a table if some conditions are met (specific type, type_no and positive value). Amount column is of type double.

This is the query:

SELECT SUM(amount) FROM gl_trans WHERE type='0' AND type_no='1' AND amount>0;

And this is the gl_trans table - it represents the depreciation of fixed asset:

# counter, type, type_no, tran_date, event_date, doc_date, account, memo_, amount, dimension_id, dimension2_id, person_type_id, person_id
'1', '0', '1', '2018-02-28', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'2', '0', '1', '2018-02-28', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'3', '0', '1', '2018-03-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'4', '0', '1', '2018-03-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'5', '0', '1', '2018-04-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'6', '0', '1', '2018-04-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'7', '0', '1', '2018-05-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'8', '0', '1', '2018-05-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'9', '0', '1', '2018-06-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'10', '0', '1', '2018-06-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'11', '0', '1', '2018-07-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'12', '0', '1', '2018-07-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'13', '0', '1', '2018-08-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'14', '0', '1', '2018-08-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'15', '0', '1', '2018-09-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'16', '0', '1', '2018-09-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'17', '0', '1', '2018-10-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'18', '0', '1', '2018-10-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'19', '0', '1', '2018-11-30', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'20', '0', '1', '2018-11-30', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'
'21', '0', '1', '2018-12-31', '0000-00-00', '0000-00-00', '071-1-1', '', '-2', '0', '0', '6', '0001'
'22', '0', '1', '2018-12-31', '0000-00-00', '0000-00-00', '401-1', '', '2', '0', '0', '6', '0001'

So I did: (db_query and db_fetch_row are custom functions - they are 100% ok)

$sql = "SELECT SUM(amount) FROM gl_trans WHERE type=".db_escape($type)." AND type_no=".db_escape($type_no)." AND amount>0";
$result = db_query($sql, "query for gl trans total");     
$row = db_fetch_row($result);
return $row[0];

The result was null. While debuging I discovered that query for amount column... returns 0: 

$sql = "SELECT amount FROM gl_trans WHERE type=".db_escape($type)." AND type_no=".db_escape($type_no);
$result = db_query($sql, "blah blah blah");
while ($test = db_fetch_assoc($result)) {
  var_dump($test); // [amount] => 0 !!!
}

I pasted the same exact query: 

SELECT SUM(amount) FROM gl_trans WHERE type='0' AND type_no='1' AND amount>0

...into the Workbench and it gave me correct result (22) !

I tried to perform similar queries on different tables (just switching the table and column) and they were ok.
I also tried to sum up another field in this table (person_type_id) and the query was also successful.
Finally we have pasted the code to my coworker's laptop (same database, same system - Ubuntu, same code), and it worked.

Somehow on my computer, with my settings, just this one column in the whole database is always 0 and only if I query for it from the code.
Code is ok - it works on other tables and columns
Db is ok - queries from Workbench and console works.
I suspect some settings issue or possibly some kind of bug but I have no idea what could be wrong.

JustBaron
  • 2,319
  • 7
  • 25
  • 37
Tompo
  • 15
  • 6
  • What do you get when you var_dump $sql in the last code chunk? – MandyShaw Jun 28 '18 at 13:38
  • 1
    What is `db_query()` and `db_fetch_assoc()` and `db-fetch_row()` they are not vanilla PHP – RiggsFolly Jun 28 '18 at 13:39
  • Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Jun 28 '18 at 13:44
  • As @RiggsFolly mentioned, db_query() is not vanilla PHP, but it is a Drupal method. If you are working in Drupal this would be more appropriate for the Drupal stackexchange, but I can give you a hand writing this out the Drupal way. Just let me know if this is for 7 or 8. – greendemiurge Jun 28 '18 at 13:49
  • Those are custom functions as I mentioned. But they are tested and I'm sure they work. – Tompo Jun 28 '18 at 13:50
  • 1
    how can we blindly trust your custom functions just because you said "they are ok"? Being skeptical is a a rule of thumb over here. The queries are ok. The rest of the PHP code as well. You say you receive correct result in workbench with the same query. So the custom functions might indeed be the root cause of your issues – Thomas G Jun 28 '18 at 13:50
  • **If you look CLOSELY** at the data you show us. There are matching `2` and `-2` values in `amount` The `SUM()` of which is ZERO – RiggsFolly Jun 28 '18 at 13:53
  • It's not Drupal, it's an old legacy code, a king of accounting software, some parts even 10 or more years old. – Tompo Jun 28 '18 at 13:54
  • @RiggsFolly I noticed that too – Thomas G Jun 28 '18 at 13:54
  • _Small Point_ ___10 or more years old___ that makes them old not right – RiggsFolly Jun 28 '18 at 13:54
  • The sum of amount>0 should not be 0. – Tompo Jun 28 '18 at 13:55
  • Ah... good point – RiggsFolly Jun 28 '18 at 13:56
  • Yes, I know they are old, I mean - they work in other functions (in hundreds of them). – Tompo Jun 28 '18 at 13:59
  • I tried with vanilla functions mysqli_query and mysqli_fetch_row - same result... – Tompo Jun 29 '18 at 06:22

2 Answers2

0

It looks like you are missing single quotes in your query:

$sql = "SELECT SUM(amount) FROM gl_trans WHERE type='".db_escape($type)."' AND type_no='".db_escape($type_no)."' AND amount>0";

Your code is not safe against SQL Injection. You should use PDO or prepared statements instead of the custom functions you have. Read more about it here

Checking your $sql with $var_dump() is also helpful

Kevin
  • 22
  • 4
  • Thanks, but it's not that. db_escape adds quotes. I also tried this with vanilla mysqli_query and mysqli_fetch_row - same result. – Tompo Jun 29 '18 at 06:24
  • Ah ok. Can you echo or do a var_dump of $sql to see what is being queried? Is it the same query as what you tried in the workbench? – Kevin Jun 29 '18 at 17:42
  • Thanks, I finally found it - take a look at my answer. – Tompo Jul 04 '18 at 12:55
0

Question can be closed. The problem was elsewhere - someone mistakenly left a function hidden deep in the code that was voiding all the amounts before my function queried for them. I haven't noticed that due to transaction failure somewhere else.

Tompo
  • 15
  • 6