通过$ _POST数组循环的PDO插入语句 [英] PDO insert statement with loop through $_POST array
问题描述
我该如何遍历$ _POST数组(其中$ _POST表单名称等于数据库列名称),并安全地从MySQL注入中做到这一点?
How can I loop through $_POST array where $_POST form names are equal to database columns names, and do it safely from MySQL injections?
我将收到大约40个输入的表单.
例如,我使用html格式( index.php ):
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>
我有 form.php 文件:
$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']
));
如何为$ _POST建立一个foreach循环,所以我不需要为40个$ _POST变量编写此代码?
How can I build a foreach loop for $_POST, so I don't need to write this code for 40 $_POST variables?
推荐答案
正如其他人所述,恶意用户仍然有可能编辑DOM中的字段名称,但是这可能引起人们的兴趣.
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 );
为回答有关从输入数据中删除虚假html的问题,您可以尝试以下方法:
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
我使用PDO尝试了此操作,并在值中包含了DROP
语句,这没关系-已作为字符串数据插入.当我尝试修改字段名称时,它导致PDOException
,而没有执行其他操作....
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 );
这篇关于通过$ _POST数组循环的PDO插入语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!