Escaping a string with quotes in Laravel

Question

I would like to insert the content of an excel file into my database.

I simply use a raw query to achieve this.

---

The controller function

public function uploadExcel()
{
    $filename = Input::file('import_file')->getRealPath();

    $file = fopen($filename, "r");

    $count = 0;
    while (($emapData = fgetcsv($file, 10000, "\t")) !== FALSE) {
        $count++;

        if($count>1) {
            DB::statement("INSERT INTO `members` (
                member_title,
                member_first_name,
                member_name_affix,
                member_last_name,
                member_private_address,
                member_private_zip_code,
                member_private_location,
                member_private_phone,
                member_private_mobile,
                member_private_fax,
                member_private_mail,
                member_business_position,
                member_business_name,
                member_business_address,
                member_business_zip_code,
                member_business_location,
                member_business_area_code,
                member_business_phone,
                member_business_fax,
                member_business_mobile,
                member_business_mail,
                member_join_date,
                extra
            ) VALUES (
                '$emapData[0]',
                '$emapData[1]',
                '$emapData[2]',
                '$emapData[3]',
                '$emapData[4]',
                '$emapData[5]',
                '$emapData[6]',
                '$emapData[7]',
                '$emapData[8]',
                '$emapData[9]',
                '$emapData[10]',
                '$emapData[11]',
                '$emapData[12]',
                '$emapData[13]',
                '$emapData[14]',
                '$emapData[15]',
                '$emapData[16]',
                '$emapData[17]',
                '$emapData[18]',
                '$emapData[19]',
                '$emapData[20]',
                '$emapData[21]',
                '$emapData[22]'
            )");
        }
    }
    return redirect('index.index');
}

---

My Problem: There are names in the excel file like Mc'Neal, so I get an error message.
How can I escape the apostrophe in laravel??

I am really new to laravel and would be happy for any kind of help!

Skewness

Why don't you populate a member model and save()? – Bawcock

Answer 1

have you tried addslashes()?

http://php.net/manual/en/function.addslashes.php

Bidding

No! Thank you!! However, I have to pass a string to that function and $emapData is an array.. – Skewness

try something like array_map( "escapeFunction" ,$emapData) ... php.net/manual/en/function.array-map.php – Bidding

Thank you! I found the solution here -> https://mcmap.net/q/1164555/-php-addslashes-using-array – Skewness

This is NOT the best approach. The page you link to specifically says: "The addslashes() is sometimes incorrectly used to try to prevent SQL Injection. Instead, database-specific escaping functions and/or prepared statements should be used." What you likely want is: php.net/manual/en/mysqli.real-escape-string.php – Jenette

Addslashes is not suitable for escaping database content, as @Jenette indicates. – Priedieu

Answer 2

To escape strings with single quotes for MS SQL, we would need to escape it by adding an another single quote.

The following function does this. So, you may try using this function:

public static function mssql_escape($unsafe_str) 
{
    if (get_magic_quotes_gpc())
    {
        $unsafe_str = stripslashes($unsafe_str);
    }
    return $escaped_str = str_replace("'", "''", $unsafe_str);
}
//for example $unsafe = "AB'CD'EF";
$escaped = mssql_escape($unsafe);
echo $escaped;// Would output the escaped string as  "AB''CD''EF"

Adriene