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!

Why don't you populate a `member` model and save()? – Mark Baker Aug 31 '16 at 23:21 — Mark Baker, Aug 31 '16 at 23:21

score 9 · Accepted Answer · answered Aug 31 '16 at 22:42

9

have you tried addslashes()?

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

answered Aug 31 '16 at 22:42

Sherif

1,477
1
14
21

No! Thank you!! However, I have to pass a string to that function and $emapData is an array.. – Schwesi Aug 31 '16 at 22:45
2

try something like `array_map( "escapeFunction" ,$emapData)` ... http://php.net/manual/en/function.array-map.php – Sherif Aug 31 '16 at 22:48
Thank you! I found the solution here -> http://stackoverflow.com/questions/19210833/php-addslashes-using-array – Schwesi Aug 31 '16 at 22:53
3

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: https://www.php.net/manual/en/mysqli.real-escape-string.php – ShaneMit Apr 23 '19 at 19:17
Addslashes is not suitable for escaping database content, as @ShaneMit indicates. – Elliot May 23 '20 at 12:10

Rahul Gupta · Answer 2 · 2017-09-06T13:06:39.923

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"

Escaping a string with quotes in Laravel

2 Answers2

Linked