How to escape single quote in laravel Raw query

Question

Previously, I was inserting data from csv to database using eloquent. However, I feel this quite slow, so I am trying to test it with Raw Query as follows in laravel:

$query = Committee::where('name', $committee_arr['name']);

if ($committee_arr['mailing_address_city']) {
    $query->where('mailing_address_city', $committee_arr['mailing_address_city']);
}
if ($committee_arr['mailing_address_state']) {
    $query->where('mailing_address_state', $committee_arr['mailing_address_state']);
}
if ($committee_arr['mailing_address_zip']) {
    $query->where('mailing_address_zip', $committee_arr['mailing_address_zip']);
}

$committee = $query->first();

if ($committee) {
    $committee_arr['updated_at'] = date('Y-m-d H:i:s');
    $committee_arr['updated_by'] = $this->data['user_id'];
    try {
        $valueSets = array();
        foreach ($committee_arr as $k => $v) {
            $valueSets[] = $k . " = '" . $v . "'";
        }
        DB::update("Update `fec` set " . implode(", ", $valueSets) . " where id = " . $committee->id);
        $committee_id = $committee->id;
    } catch (\Exception $e) {
        $error_encountered = true;
        $this->error_arr[] = $e->getMessage();
        $this->error_row_numbers[] = $this->data['row_value'];
    }

} else {
    $committee_arr['created_at'] = date('Y-m-d H:i:s');
    $committee_arr['created_by'] = $this->data['user_id'];
    try {
        $columns = implode(", ", array_keys($committee_arr));
        $values = "'" . implode("', '", array_values($committee_arr)) . "'";
        DB::insert("INSERT INTO `fec`($columns) VALUES ($values)");
        $committee_id = DB::getPdo()->lastInsertId();
    } catch (\Exception $e) {
        $error_encountered = true;
        $this->error_arr[] = $e->getMessage();
        $this->error_row_numbers[] = $this->data['row_value'];
        DB::rollback();
    }

}

However, in this process I encounter one problem of these single quotes. I don't know how to use mysqli_real_escape_string in Laravel. I have also noticed about addslashes but I don't feel it's the ideal way.

So, is there any way to deal with this? Further more if you have any idea of inserting with something like parameter binding then you are welcome.

If you wonder what does that $committee_arr contains then it looks like below:

Array
(
    [name] => O'KANE, PATRICK M
    [mailing_address_city] => SAN ANTONIO
    [mailing_address_state] => TX
    [mailing_address_zip] => 782583940
    [employer] => ENERGY TRANSFER EMPLEE MGMT CO
    [occupation] => SR DIRECTOR - PL HEDGNG/COMMOD
)

Futher Clarification:

My main concern is to handle O'Kane properly. But, if I have to say it exactly, then I want to handle with something like below. But, the problem with it is that, these fields and value are totally dynamic. It will be in array as shown above. Not limiting to above six fields and values, there may be more than that as well.

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), array(
   'somevariable' => $someVariable,
 ));

Just to clarify, your concern would be the quote as in **O'KANE**. Am I right? — Francinaldo Almeida, Apr 17 '19 at 04:33
Maybe helpful:- [Escaping a string with quotes in Laravel](https://stackoverflow.com/questions/39260080/escaping-a-string-with-quotes-in-laravel/39260126) — Alive to die - Anant, Apr 17 '19 at 04:36
@FrancinaldoAlmeida I have just updated the question. Please, have a look. — Aayush Dahal, Apr 17 '19 at 05:58
@AlivetoDie I have already mentioned about `addslashes` in question. Instead do you have any option something like `mysqli_real_escape_string($con,$string)` for laravel — Aayush Dahal, Apr 17 '19 at 05:59
check this thread:- https://stackoverflow.com/a/12623197/4248328 . Use PDO statements. everything will be sorted out automatically — Alive to die - Anant, Apr 17 '19 at 06:12

score -1 · Answer 1 · answered Apr 17 '19 at 07:06

Try addslashes()function for associative array like below.

$committee_arr=array_map('addslashes', $committee_arr);
print_r($committee_arr);

You will get array like below.

Array
(
    [name] => O\'KANE, PATRICK M
    [mailing_address_city] => SAN ANTONIO
    [mailing_address_state] => TX
    [mailing_address_zip] => 782583940
    [employer] => ENERGY TRANSFER EMPLEE MGMT CO
    [occupation] => SR DIRECTOR - PL HEDGNG/COMMOD
)

How to escape single quote in laravel Raw query

Futher Clarification:

1 Answers1