php pdo使用函数插入数据

Question

I'm trying to insert the datas with php & pdo. I'm writing this below codes for insert function and I call them like this php_insert_data("admin", array( 'user_name', 'user_pwd', 'user_email', 'profile_created_on' ), array( 'Administrator', '123456', '[e-mail]', 'date here' ));. I tried this below code. But it doesn't insert the datas into db table. How do I pass these above column names and column values properly?

function php_insert_data($table_name, array $field_name, array $field_values)   
    {
        global $dbh;
        foreach($field_name as $f_names)
        {
            $transform_array_fnames[] = $f_names;
        }
        foreach($field_values as $f_values)
        {
            $transform_array_fvalues[] = $f_values;
        }
        $comma_fnames = implode(',', $transform_array_fnames);
        $comma_fvalues = implode(',', $transform_array_fvalues);
        $insert_query = $dbh->prepare("INSERT INTO $table_name SET $comma_fnames = $comma_fvalues");
        $insert_query->bindValue(':comma_fvalues', $comma_fvalues);
        $insert_query->execute();
    }

Answer 1

Ultimately your prepared query will look something like:

INSERT INTO admin SET user_name = Administrator

You are missing the quotes around Administrator which is not a valid column in the table. Hence you will get an SQL error. You can see this by turning error reporting on and up.

Using variables in your queries makes them vulnerable to injection, so it's generally something to avoid -- even if the variables come from a trusted location.

You are using bindValue incorrectly. For it to work you would need a single field in the query named :comma_fvalues.

You can rewrite this as:

$field_value_tokens = array_fill(0, count($f_values), "?");
$field_value_tokens_commas = implode(",", $field_value_tokens);
$insert_query = $dbh->prepare("INSERT INTO $table_name ($comma_fnames)
    VALUES ($field_value_tokens_commas)");
$insert_query->execute($f_values);