mysqli_real_escape_string deleting values

Question

I'm getting null values after I run the DBEscape($data) function that is for SQL injection protection. Can someone help?

My inputs are all multiple arrays, ex: name="quote[][dt_flight]", name="quote[][acft]", etc.

Method is POST.

function DBEscape($data){
    $link = DBConect();
    if(!is_array($data)){
        $data = mysqli_real_escape_string($link,$data);
    }
    else {
        $arr = $data;
        foreach ($arr as $key => $value){
            $key = mysqli_real_escape_string($link, $key);
            $value = mysqli_real_escape_string($link, $value);
            $data[$key] = $value;
        }
    }
    DBClose($link);
    return $data;
}



    function DBCreate($table, array $data, $insertId = false){
        $table = DB_PREFIX.'_'.$table;
        $data = DBEscape($data);
        var_dump($data);
        $fields = implode(", ", array_keys($data));
        $values = "'".implode("', '", $data)."'";
        $query = "INSERT INTO {$table} ({$fields}) VALUES ({$values});";
        var_dump($query);
        return DBExecute($query, $insertId);
    }


if(isset($_POST["quote"]) && is_array($_POST["quote"])){
    foreach($_POST["quote"]["dt_flight"] as $key => $text_field){
        
        $last_id = DBCreate('quote',$_POST['quote'],true);
       
       $i++;
    }  
}

The connection works since it is inserting the rows into the tables. I used vardump before and after the DBEscape to figure out that it is deleting the values, the keys are fine.

PS: The proposed answer is for a single variable not an array.

Is the return of `DBConect()` an instance of `mysqli` as this is what `mysqli_real_escape_string()` is expecting as it's first parameter. — Nigel Ren, Jun 02 '18 at 09:56
No, `DBConect()` is function where I get the parameters and connect to the DB. — Philippe Winter, Jun 02 '18 at 10:38
`DBEscape` itself looks fine to me (one remark though, posting that one in a seperate comment. Please insert a `var_dump($data)` at the first line of `DBCreate` and verify you pass the expected array-values. If so, please post them here (so we have more info to help you). If not, your problem isn't with `DBEscape` and you have to take a step back and look what is going wrong within your `foreach`. — Peter van der Wal, Jun 02 '18 at 10:56
`mysqli_real_escape_string` **isn't a safe way** to escape column-names (or table-names and so on) - it should be used for values only, thus anything you put single or double quotes around. `mysqli_real_escape_string(" acft ) VALUES ( SELECT password FROM users LIMIT 1 ) ; -- ")` for example would be placed in your query **without any protection!!!** Instead, provide a list of known columns (for example as an extra argument on `DBCreate`) and use `array_intersect` to filter out any invalid keys (non existing column-names and/or attacks on your data). — Peter van der Wal, Jun 02 '18 at 11:06
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Progman, Jun 02 '18 at 13:24
Vardump at first line of `DBCreate`: `array(5) { ["dt_flight"]=> array(1) { [0]=> string(10) "2018-06-13" } ["acft"]=> array(1) { [0]=> string(6) "VQ-BFD" } ["origin"]=> array(1) { [0]=> string(3) "www" } ["destination"]=> array(1) { [0]=> string(3) "www" } ["fbo"]=> array(1) { [0]=> string(3) "www" } }` — Philippe Winter, Jun 02 '18 at 22:02
It's been a while since I've been online. Maybe it is long overdue, but I noticed nobody had answered after your last response to my comment - so see below — Peter van der Wal, Jun 07 '18 at 19:29

score 0 · Answer 1 · answered Jun 07 '18 at 19:27

As you can see in your var_dump-result, the data you sent to DBCreate and thus to DBEscape looks like

array(
    'dt_flight' => array(0 => '2018-06-13'),
    'acft'      => array(0 => 'VQ-BFD',
    // and so on
)

Therfore the data you sent to

// $value = array(0 => '2018-06-13') here
$value = mysqli_real_escape_string($link, $value);

And well, mysqli_real_escape_string doesn't like arrays very much, thus will return NULL and thus inserting empty data in your table.

You most likely want to resolve this error within your foreach($_POST["quote"]["dt_flight"]) loop, since I suppose you sent multiple flight-data:

foreach($_POST["quote"]["dt_flight"] as $key => $text_field) {
    // $key would be 0, for $_POST["quote"]["dt_flight"][0] = '2018-06-13'

    $keyData = [];
    foreach($_POST["quote"] as $field => $allFieldValues) {
        // Walk over every field, and add the value for the same $key
        if (is_array($data) && isset($allFieldValues[$key])) {
            // Would add for example $keyData['acft'] = $_POST['quote']['acft'][0] = 'VQ-BFD';
            $keyData[$field] = $allFieldValues[$key];
        }
    }

    var_dump($keyData); 
    // Would look like array(
    //    'dt-flight' => '2018-06-13',
    //    'acft'      => 'VQ-BFD',
    //    and so on
    // )

    $last_id = DBCreate('quote',$keyData,true);

    $i++;
}

Although this is not part of your question, I really suggest you also take care of my comment on your question about mysqli_real_escape_string not being a safe way to escape column-names (or table-names and so on). For example with following solution:

function DBCreate($table, array $data, $insertId = false) {
    // For each table the known columns
    $columns = array( 'quote' => array('dt_flight', 'acft', '...') );

    // Verify valid table given
    if (!isset($columns[$table])) {
        throw new InvalidArgumentException('No such table: ' . $table);
    }

    // Remove everything from data where the key is not in $columns[$table] 
    // = Remove everything where the column-name is non-existing or even an attempt to hack your system
    $data = array_intersect_key($data, array_fill_keys($columns[$table], null));

    if (!count($data)) {
        throw new InvalidArgumentException('No (valid) data given at all');
    }

    // Next, continue with your implementation
}

mysqli_real_escape_string deleting values

1 Answers1