Sanitize array using array_map

Question

I am trying to array_map to sanitize an array, which I have created from a csv file. Here's my code:

if (isset($_FILES['csv']['size'])) {
    if ($_FILES['csv']['size'] > 0 && $_FILES['csv']['size'] != NULL  ) { 
        //Clear existing qty_csv table
        mysqli_query($conn,'TRUNCATE TABLE qty_csv');
        $row_count  = 0;
        //get the csv file 
        $filename = $_FILES['csv']['tmp_name']; 
        $handle = fopen($filename,"r"); 
        $delimiter = ',';
    $unescapedArray = array();
        $data = csv_to_array($filename,$delimiter);

function array_map_callback($a)
{
  global $conn;
  return mysqli_real_escape_string($conn, $a);
}

$data2 = array_map('array_map_callback',$data);

Whenever I run my bit of code I get the warning:

Warning: mysqli_real_escape_string() expects parameter 2 to be string, array given in C:\xampp\htdocs\

Why does this happen, and how can I fix it?

This is the structure of the original data:

part_code varchar(20)
part_descr varchar(255)
part_location varchar(20)
part_qty_in_stock int(11)
reorder_level int(11)
reorder_qty int(11)
part_price decimal(6,2)

Answer 1

This is what people in the comments were talking about with prepared statements. The statement is pre-loaded with ? placeholders, and then each of the placeholders is bound to a variable.

So file() gives us each line of the file in an array element which we can easily loop through with foreach. Within the loop, we use str_getcsv() to turn each CSV line into an array (though if you prefer to roll your own, be my guest) and execute the prepared statement.

Every time the statement is executed, the bound variable value is checked and placed into the statement. The overhead of setting up the database is only done once, resulting in a lot less overhead. Plus you get the bonus of not needing to escape strings; MySQL does it for you.

Of course for production code you'd want to include checks to make sure statement preparation, variable binding, and execution don't throw any errors. Also you didn't include a CSV sample, so you may have to allow for any non-standard separators or terminators in str_getcsv().

//assuming you have up here something like this:
$conn = new mysqli($host, $user, $pass, $dbase);

if (!empty($_FILES['csv']['size'])) {
    //Clear existing qty_csv table
    $conn->query('TRUNCATE TABLE qty_csv');
    //get the csv file 
    $filename = $_FILES['csv']['tmp_name'];
    $data = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    $row_count  = count($data);
    //value has to exist for bind_param to work
    $csv = str_getcsv($data[0]);
    $query = "INSERT INTO table (part_code, part_descr, part_location, part_qty_in_stock, reorder_level, reorder_qty, part_price) VALUES (?,?,?,?,?,?,?)";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("sssiiid", $csv[0], $csv[1], $csv[2], $csv[3], $csv[4], $csv[5], $csv[6]);
    foreach ($data as $row) {
        $csv = str_getcsv($row);
        $stmt->execute();
    }
}

Answer 2

The error is caused by an item in $data which is not a string. Do a var_dump to see what's inside the $data before passing it to array map. Or you could do something like:

function array_map_callback($a)
{
    global $conn;
    if (is_array($a) {
         foreach($a as $idx => $item) {
            $a[$idx] = mysqli_real_escape_string($conn, $item)
         }
         return $a;
     } else {
         return mysqli_real_escape_string($conn, $a);
    }
}

But this is just a possible solution, it may be better to find out why you have a non-string in your $data array, and make sure it doesn't get there.