CODEWITHSUNDEEP
phpmysqlsqlcodeignitercontroller
Iishfaaq Ismath
Iishfaaq Ismath

Reputation: 77

Injecting an SQL to Controller in codeignitor

I am trying to inject a SQL with a DATE_FORMAT method and it gives me parsing error. But the query working perfectly in phpmyadmin. Below you can find my code which is my controller

public function index() {
  $sql = "SELECT DATE_FORMAT('added_date', "%M") AS Month, SUM(total) FROM tbl_order GROUP BY DATE_FORMAT('added_date', "%M")";
  $query = $this->db->query($sql);
  $orderData= $query->result_array();
  $data["orderData"] = $orderData;
  var_dump($data);die;
  $this->load->view('admindashboard/index.php',$data);
}

I have attached the error message as wellenter image description here

Upvotes: 1

Views: 54

Answers (3)

tadman
tadman

Reputation: 211740

If added_date is a column you must pay careful attention to the quotes used. In MySQL columns are delimited with backticks, but this is only necessary if they conflict with any reserved keywords.

The other problem is using both double quotes to define the string and within the string. If you do that you must escape the inner ones, like this:

 $query = $this->db->query("SELECT DATE_FORMAT(added_date, \"%M\") AS Month, SUM(total) FROM tbl_order GROUP BY Month");

If you have a syntax highlighting text editor, which is pretty much essential these days, you'd see how the string highlighting stops immediately on what's perceived as a closing quote in your original code. That's a warning you've got a syntax error.

The other fix is to use two different kinds of quotes. MySQL's default mode permits either double or single quotes for string values:

 $query = $this->db->query('SELECT DATE_FORMAT(added_date, "%M") AS Month, SUM(total) FROM tbl_order GROUP BY Month');

In practice it's usually better to use single quoted strings for queries for the singular reason that accidental SQL injection is prevented: $-type variables are not interpolated in single quote mode.

Upvotes: 0

Asif Thebepotra
Asif Thebepotra

Reputation: 340

i suggest to use single quote for whole string and inside it use double quotes.it will solve error i think.

$sql = 'SELECT DATE_FORMAT("added_date", "%M") AS Month, 
SUM(total) FROM tbl_order 
GROUP BY DATE_FORMAT("added_date", "%M")';

Upvotes: 3

D-Shih
D-Shih

Reputation: 46249

I think you can try to use '%M' instead of "%M", because it will split the string by ".

$sql = "SELECT DATE_FORMAT(added_date, '%M') AS Month, 
SUM(total) 
FROM tbl_order 
GROUP BY DATE_FORMAT(added_date, '%M')";

Upvotes: 4

Related Questions