CodeIgniter Forums
Escape Array in SQL. How to do it? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Escape Array in SQL. How to do it? (/thread-60253.html)



Escape Array in SQL. How to do it? - El Forum - 02-14-2014

[eluser]behnampmdg3[/eluser]
Hi;

Escaping only works within a quoted SQL string. It prevents the user from breaking out of this string. In this SQL below since I insert the value directly into the query without quoting it, the escaping is completely pointless. It has no effect at all. And if it did, you'd break the query. Because then you'd have an IN expression with a single big string in it.

How can I quote and escape the invidual IDs?
Code:
$sql = "SELECT
           *
       FROM   ad_have
           INNER JOIN members
             ON members.id = ad_have.member_id
         INNER JOIN au_postcodes
             ON au_postcodes.id = ad_have.suburb
         LEFT OUTER JOIN
          (SELECT ad_ID, ad_have_photo.photo FROM ad_have_photo GROUP BY ad_ID) AS Q_PHOTO  ON Q_PHOTO.ad_ID = ad_have.id
                
       WHERE ad_have.active = 'y'
       AND ad_have.id IN ".$this->db->escape_str($ad_ids)."
       AND ad_have.weekly_rent > 0
       ORDER BY ad_have.id DESC ";



Escape Array in SQL. How to do it? - El Forum - 02-14-2014

[eluser]behnampmdg3[/eluser]
Would this work?
Code:
$sql = "SELECT
          *
       FROM   ad_have
           INNER JOIN members
             ON members.id = ad_have.member_id
         INNER JOIN au_postcodes
             ON au_postcodes.id = ad_have.suburb
         LEFT OUTER JOIN
          (SELECT ad_ID, ad_have_photo.photo FROM ad_have_photo GROUP BY ad_ID) AS Q_PHOTO  ON Q_PHOTO.ad_ID = ad_have.id
                
       WHERE ad_have.active = 'y'
       AND ad_have.id IN ?
       AND ad_have.weekly_rent > 0
       ORDER BY ad_have.id DESC ";
       $query = $this->db->query($sql, array($ad_ids));



Escape Array in SQL. How to do it? - El Forum - 02-15-2014

[eluser]Karman de Lange[/eluser]
This How I do it .. But I don't ever pass the ID from the front end so not bothered with sql injection.

EDIT: Reading your post properly now... I will see how one can do this properly Smile


Code:
##If Add_ids is array, then convert to string otherwise just leave as is.
       $ad_ids = is_array($ad_ids) ? implode(',', $ad_ids) : $ad_ids;


$sql = "SELECT
           *
       FROM   ad_have
           INNER JOIN members
             ON members.id = ad_have.member_id
         INNER JOIN au_postcodes
             ON au_postcodes.id = ad_have.suburb
         LEFT OUTER JOIN
          (SELECT ad_ID, ad_have_photo.photo FROM ad_have_photo GROUP BY ad_ID) AS Q_PHOTO  ON Q_PHOTO.ad_ID = ad_have.id
                
       WHERE ad_have.active = 'y'
       AND ad_have.id IN ($ad_ids)
       AND ad_have.weekly_rent > 0
       ORDER BY ad_have.id DESC ";



Escape Array in SQL. How to do it? - El Forum - 02-15-2014

[eluser]Karman de Lange[/eluser]
array(1,2,3,4,5,6) becomes:
'1','2','3','4','5','6'

Code:
if (is_array($branch_ids))
        {
            foreach ($branch_ids as $key => $branch_id)
            {

                $branch_ids[$key] = $this->db->escape($branch_id);
            }

            $branch_ids = implode(',', $branch_ids);

        }