doumu9799
doumu9799
2018-10-30 18:44
浏览 132
已采纳

在codeigniter中向SQL注入一个SQL

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • drgwsx8405
    drgwsx8405 2018-10-30 18:48
    已采纳

    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')";
    
    点赞 评论
  • doubianyan9749
    doubianyan9749 2018-10-30 18:57

    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")';
    
    点赞 评论
  • dsfdsf21321
    dsfdsf21321 2018-10-30 19:32

    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.

    点赞 评论

相关推荐