GROUP BY not work during concat

Question

i try to put GROUP as concat in my PHP-Firebird. But it seems not work. my script like:

 $sGroupBy = " GROUP BY LINE_NAME ";

 $sQuery = "SELECT ".$sLimit." LINE_NAME, MODEL_ONLY, VER_ONLY, PROD_NO, 
            LOT_SIZE, START_SERIAL, SERIAL_NO_LOW, SERIAL_NO_UP, PROD_DATE 
            FROM DOC_TO".$sWhere.$sGroupBy.$sOrder.";";

how do i do to resolve this problem? have you some sites or tutorial about PHP-CONCATENATION that i can learn more completely? thanks for advance.

$sOrder = " ORDER BY PROD_DATE DESC ";

$sWhere = " WHERE (LINE_NAME LIKE '".$_POST['sSearch']."%' OR
          MODEL_ONLY LIKE '%".$_POST['sSearch']."%' OR ".
          " VER_ONLY LIKE '%".$_POST['sSearch']."%' OR ".
          " LOT_SIZE LIKE '%".$_POST['sSearch']."%' OR ".
          " START_SERIAL LIKE '%".$_POST['sSearch']."%' OR ".
          " SERIAL_NO_LOW LIKE '%".$_POST['sSearch']."%' OR ".
          " SERIAL_NO_UP LIKE '%".$_POST['sSearch']."%' OR ".
          " PROD_NO LIKE '%".$_POST['sSearch']."%' OR ".
          " PROD_DATE LIKE '%".$_POST['sSearch']."%') ";
$sLimit = "";
if ( isset( $_POST['iDisplayStart'] ) )
{
  settype($iDisplayStart,'integer');
  $iDisplayStart = $_POST['iDisplayStart'];
  $iDisplayLength = $_POST['iDisplayLength'];
  $sLimit = sprintf(" FIRST %d SKIP %d ",$iDisplayLength,$iDisplayStart);
}

Can you show us what $sWhere and $sOrder look like? Your SQL should work fine assuming there are spaces on the edges of the variables like $sGroupBy has — pinkfloydx33, Nov 22 '10 at 09:23
Can you provide us with an example of how $sQuery looks when all parameters are set? (i.e. the query you are sending to Firebird) — Guillem Vicens, Nov 22 '10 at 10:30

score 1 · Accepted Answer · edited May 23 '17 at 12:19

SELECT X, Y, Z GROUP BY X makes no sense; you need to group by Y and Z as well, or use them in an aggregate function. DISTINCT also works, see postgreSQL group by different from mysql?.

You're also using user input directly while building a query string, which is an invitation for SQL injection. Use parameterized queries. (Escaping user input also seems to be a popular solution though I cannot figure out why.) This is assuming you're not replacing $_POST contents with escaped strings or using magic quotes, both of which would be bad practice, but nothing as serious as an SQL injection vulnerability.

If you think there's something wrong with your SQL query, the first thing you should do is to print it out and examine it. As it stands, a simple sSearch input like don't could have broken your code.

I don't understand. You need to show us examples of actual queries going to Firebird. — aib, Nov 23 '10 at 11:09

GROUP BY not work during concat

1 Answers1