How to Escaping Single Quotes With mysqli_real_escape_string?

Question

My code is like this :

My array of city (echo '<pre>';print_r($city);echo '</pre>';die();) :

Array
(
    [0] => Array
        (
            [CityCode] => 14879
            [CityName] => Soldeu
        )

    [1] => Array
        (
            [CityCode] => 14881
            [CityName] => Ari'nsal
        )

    [2] => Array
        (
            [CityCode] => 14882
            [CityName] => El Tarter
        )

    [3] => Array
        (
            [CityCode] => 14883
            [CityName] => Grau Roig
        )

    [4] => Array
        (
            [CityCode] => 175198
            [CityName] => Llorts
        )

)

In city code : 14881, city name : Ari'nsal

It's single quote in string.

I try code like this :

$date = date('Y-m-d H:i:s');   

            $sql = "INSERT INTO hotel_search_city (nation_code, city_code, city_name, created_at, updated_at) values ";

            $valuesArr = array();
            foreach($city as $row){

                $nation_code = $value->nation_code;

                $city_code = $row['CityCode'];
                $city_name = mysqli_real_escape_string($row['CityName']);
                $created_at = $date;
                $updated_at = $date;

                $valuesArr[] = "('$nation_code', '$city_code', '$city_name', '$created_at', '$updated_at')";
            }

            $sql .= implode(',', $valuesArr);

            $query = $sql;
            $this->db->query($query);

There exist error like this : Message: mysqli_real_escape_string() expects exactly 2 parameters, 1 given....

Any solution to solve my problem?

Thank you very much

If you're not using the object-oriented syntax, then you need to provide the connection: `mysqli_real_escape_string($this->db, $row['CityName'])`. Better yet, don't use `mysqli_real_escape_string`, but PDO and prepared statements. — Amadan, Mar 04 '16 at 05:56
I try : `mysqli_real_escape_string($this->db, $row['CityName'])`. There exist error : Message: mysqli_real_escape_string() expects parameter 1 to be mysqli, object given — moses toh, Mar 04 '16 at 06:46
I see. Then try `$this->db->mysql_real_escape_string($row['CityName'])`. — Amadan, Mar 04 '16 at 06:49
@Amadan, There exist error : Fatal error: Call to undefined method CI_DB_mysqli_driver::mysql_real_escape_string() in — moses toh, Mar 04 '16 at 06:56
@Amadan, I'm still confused. I try like this : http://pastebin.com/YJ2f97e8 — moses toh, Mar 04 '16 at 07:07
@Amadan, I using codeigniter. The settings in the config database — moses toh, Mar 04 '16 at 07:15
Sorry, missed that. For Codeigniter, use `$this->db->escape($row['CityName'])`. `mysqli` is the wrong tool. Even better, read about [query bindings](https://ellislab.com/codeIgniter/user-guide/database/queries.html). — Amadan, Mar 04 '16 at 07:32
@Amadan, seems to use `$this->db->escape($row['CityName'])` not the right way. But using `$city_name = addslashes($row['CityName']);` — moses toh, Mar 04 '16 at 07:46
Because when using `$this->db->escape($row['CityName'])`, the result : INSERT INTO hotel_search_city (nation_code, city_code, city_name, created_at, updated_at) values ('AD', '14879', ''Soldeu'', '2016-03-04 14:42:43', '2016-03-04 14:42:43') — moses toh, Mar 04 '16 at 07:49
`escape` adds quotes for you. You should not use them in your SQL. You should also escape all other values too, or risk a [Bobby Tables](http://bobby-tables.com/). But seriously, learn about query bindings. — Amadan, Mar 04 '16 at 07:50
@Amadan, Ok, thank you. I decided to using addslahes. It can insert into db. But there exist error like this : `Message: Illegal string offset 'CityName'` — moses toh, Mar 04 '16 at 08:23
You need to also say what line produces the error, or it is not quite useful. Also, see [mysql_real_escape_string VS addslashes](http://stackoverflow.com/questions/3473047/mysql-real-escape-string-vs-addslashes) and [What's the difference between PHP's addslashes and mysql(i)_escape_string?](http://stackoverflow.com/questions/4486016/whats-the-difference-between-phps-addslashes-and-mysqli-escape-string) to see why `addslashes` is not a good idea. — Amadan, Mar 04 '16 at 08:38

score 0 · Accepted Answer · answered Mar 04 '16 at 08:36

$date = date('Y-m-d H:i:s');   
$sql = "INSERT INTO hotel_search_city (nation_code, city_code, city_name, created_at, updated_at) values (?, ?, ?, ?, ?)";

foreach ($city as $row) {
    $nation_code = $value->nation_code;
    $city_code = $row['CityCode'];
    $city_name = $row['CityName'];
    $created_at = $date;
    $updated_at = $date;

    $fields = array($nation_code, $city_code, $city_name, $created_at, $updated_at);
    $this->db->query($query, $fields);
}

or

$date = date('Y-m-d H:i:s');   

foreach ($city as $row) {
    $fields = array(
      'nation_code' => $value->nation_code,
      'city_code' => $row['CityCode'],
      'city_name' => $row['CityName'],
      'created_at' = $date,
      'updated_at' = $date
    );

    $this->db->insert('hotel_search_city', $fields);
}

I need you help. Look here : http://stackoverflow.com/questions/38175735/how-to-get-data-in-function-extend-controller — moses toh, Jul 04 '16 at 02:23

How to Escaping Single Quotes With mysqli_real_escape_string?

1 Answers1