Magic quote and mysql_real_escape_string

Question

Here is the original code:

       if (($handle = fopen($source_file, "r")) !== FALSE) {
    $columns = fgetcsv($handle, $max_line_length, ",");
    foreach ($columns as &$column) {
        $column = str_replace(".","",$column);
    }
    while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
        while(count($data) < count($columns)) {
            array_push($data, NULL);
        }
        $c = count($data);
        for($i = 0; $i < $c; $i++) {
            $data[$i] = "'{$data[$i]}'";
        }

        $sql[] = '(' . implode(',', $data) . ", '" . $_POST['custgroup'] . "'," . $_POST['user_id'] . ')';
    }


$query = "INSERT INTO $target_table (" . implode(',', $columns) . 
      ',custgroup,user_id) VALUES ' . implode(',', $sql);


    //mysql_query($query) or trigger_error(mysql_error());
    echo $query;
    fclose($handle);
}

But once I added mysql_real_escape_string:

   $query = "INSERT INTO $target_table (" . implode(',',array_map('mysql_real_escape_string', $columns)) . 
          ',custgroup,user_id) VALUES ' . implode(',',array_map('mysql_real_escape_string', $sql));

The query will become :

INSERT INTO UserAddedRecord (lastname,firstname,ceLL,fax,email,code,custgroup,user_id) VALUES (\'Last\',\'First\',\'01122331\',\'\',\'abc@hotmail.com\',\'12345\', \'\',17)

I checked my php.ini and get_magic_quotes_gpc(),magic quote is disabled.

magic_quotes_gpc = Off

magic_quotes_runtime = Off

magic_quotes_sybase = Off

What should be the problem?or I should just apply stripslashes()?But I assume it will be used only when magic quote is enabled.

Answer 1

You should to something along the lines of:

• use mysql_real_escape_string on all string values
• cast all the interters to (int)
• after that apply the surrounding single quotes ( ' )

The point of mysql_real_escape_string is to escape the special chars within your test content so using stripslashes would take that away.

Another way to solve that is to look into preparedStatements where you don't need to to client side esacping.

Just remember that the whole point of doing this is to prevent SQL-Injection and act accordingly :)