PDO insert statement with loop through $_POST array

Question

How can I loop through $_POST array where $_POST form names are equal to database columns names, and do it safely from MySQL injections?

I will have form with about 40 inputs.

For example, I have html form (index.php):

<form action="form.php" id="myForm" method="POST" enctype="multipart/form-data">

User: <input type="text" name="owner_name"><br>
User-email: <input type="text" name="owner_email"><br>
User-id: <input type="text" name="owner_id"><br>
Pictures: <br><input name="pictures[]" type="file" size="50" maxlength="100000" multiple><br>
<input type="submit" value="Send" />
</form>

And I have form.php file:

$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

// Check connection
if ($pdo->connect_error) {
    die("Connection failed: " . $pdo->connect_error);
}

$statement = $pdo->prepare("INSERT INTO claims_motor(owner_email, owner_name, owner_id)
    VALUES(:owner_email, :owner_name, :owner_id)");
$statement->execute(array(
    "owner_email" => $_POST['owner_email'],
    "owner_name" => $_POST['owner_name'],
    "owner_id" => $_POST['owner_id']
));

How can I build a foreach loop for $_POST, so I don't need to write this code for 40 $_POST variables?

Hmm, what would happen if an user edit's your html page through an webpage inspector? Especially the values of the name tags? — Daan, Nov 02 '15 at 10:56
I also thought about that, if he edits input names, so the query results an error and stops, but user will not see the error and values will not go to the database. — AlexIL, Nov 02 '15 at 11:01

Professor Abronsius · Accepted Answer · 2015-11-02T15:25:07.323

As the others have stated, there remains the possibility that a malicious user might edit the names of fields in the DOM, but that said the following might be of interest.

$sql='insert into `claims_motor` (`'.implode( '`,`', array_keys( $_POST ) ) .'`) values (:'.implode(',:',array_keys( $_POST ) ).');';
foreach( $_POST as $field => $value ) $params[":{$field}"]=$value;

$statement = $pdo->prepare( $sql );
$statement->execute( $params );

In answer to your question about removing spurious html from the input data, you could try something along the lines of the following:

$options=array( 'flags'=>FILTER_FLAG_NO_ENCODE_QUOTES | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH | FILTER_FLAG_ENCODE_LOW | FILTER_FLAG_ENCODE_HIGH | FILTER_FLAG_ENCODE_AMP );

function filterfield( $field ){
    global $options;
    return ":".strip_tags( filter_var( $field, FILTER_SANITIZE_STRING, $options ) );
}
function filtervalue( $field ){
    global $options;
    return strip_tags( filter_input( INPUT_POST, $field,  FILTER_SANITIZE_STRING, $options ) );
}
function isfield( &$field, $key, $fields ){
    $field=in_array( $field, $fields ) ? $field : false;
}

$sql='insert into `claims_motor` (`'.implode( '`,`', array_keys( $_POST ) ) .'`) values (:'.implode(',:',array_keys( $_POST ) ).');';
foreach( $_POST as $field => $value ) $params[ filterfield( $field ) ]=filtervalue( $field );

I'm not suggesting this is a perfect solution but it more or less answers your original question. You can find out more about filters here

I tried this using PDO with an included DROP statement in the value and that was OK - was inserted as string data. When I tried modifying a field name it cause a PDOException and did nothing else....

To get the column names as you suggest you can try:-

$sql="select group_concat(`column_name`) as 'cols' 
        from `information_schema`.`columns` 
        where `table_schema`=database() and `table_name`=:table;";

$params=array(':table' => 'claims_motor');
$statement = $pdo->prepare( $sql );
$statement->execute( $params );

/* Process the recordset */
$cols=$rs->cols; /* or whatever method to access the record */


/* Filter fields that were not in form - if any */
$cols=explode( ',', $cols );
array_walk( $cols, 'isfield', array_keys( $_POST ) );
$fields = array_filter( $cols );

/* Prepare sql for the insert statment */
$sql_insert='insert into `claims_motor` (`'.implode( '`,`', $fields ) .'`) values (:'.implode( ',:', $fields ).');';

/* reset params array */
$params=array();

/* Construct new array of bound variables / params */
foreach( $_POST as $field => $value ) $params[ filterfield( $field ) ]=filtervalue( $field );

/* add the data to db */
$statement = $pdo->prepare( $sql );
$statement->execute( $params );

Thank you! It works, but is it safety if user tries to sql inject through changing input names? For example, change input name to: `owner_id'); DROP TABLE claims_motor`. or something like that? — AlexIL, Nov 02 '15 at 11:28
Is there any way to prepare from HTML code in input? For example, i can enter in form input: `
HELLO` and It works perfectly when I show results on page. So, user can insert any scripts on page. — AlexIL, Nov 02 '15 at 11:51
About columns name, maybe I can use select statement to select all column names from table and return it as array. After, when will constructs insert statement, if this column name are inside this array with columns, then the columns uses in insert statement, if no, then if statement ignores this key and continue looping? — AlexIL, Nov 02 '15 at 14:06

PDO insert statement with loop through $_POST array

1 Answers1

Linked