单行插入工作正常,但多行给出语法错误

By simon at 2018-02-28 • 0人收藏 • 58人看过

这是选择代码:

$result = $conn->query("select step from steps");

echo "<html>";
echo "<body>";
echo " <select name='step[]' multiple='multiple'>" ;

while ($row = $result->fetch_assoc()) {                         
    unset($step);
    $step = $row['step'];
    echo '<option value="'.$step.'">'.$step.'</option>';
}
echo "</select>";
插入代码:
$listno = $_REQUEST['listno'];   
$sql = "INSERT INTO checkliststeps (listno, step) VALUES ";
foreach ($_POST['step'] as $step)
    $sql .= "($listno,'$step')";

if(mysqli_multi_query($link, $sql)){
    echo "Records added successfully.";
} 
else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
错误:

错误:无法执行INSERT INTO清单teps(listno,step) VALUES (4, 'Cccccccccccccccccccccccccccccccccccccccccccccccccc')(4,'vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv')。 你的SQL语法有错误;检查相应的手册 你的MySQL服务器版本飞行使用的语法附近 '(4,' vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv ')' 位于第1行

2 个回复 | 最后更新于 2018-02-28
2018-02-28   #1

你有mysql语法错误,请参阅下面的代码与评论。

$listno = $_REQUEST['listno'];
$sql = "INSERT INTO checkliststeps (listno, step) VALUES ";

foreach($_POST['step'] as $step) {
  $sql.= "($listno,'$step'),"; //here you forgot , at end as per mysql syntex
} 
//new you have one , extra at end of sql string so you must replace that last ,
$sql = trim($sql, ",");

if (mysqli_multi_query($link, $sql))
{
echo "Records added successfully.";
}
else
{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
注意 以上创建查询的方法不是确保有人可以窥探sql注入 [更多信息](https://www.acunetix.com/websitesecurity/sql-injection/)。 避免SQL注入您必须使用[MySQL准备 声明】(https://www.w3schools.com/php/phpmysqlprepared_statements.asp) bette的方法r安全目的。

2018-02-28   #2

mysqli_multi_query将用于执行多个不同的sql语句 反对具有多个价值观的单一陈述ch不在 正确分离,如各种评论中所述。当任何形式的用户输入时 预计,这里与POST v值,你会更好地使用一个 prepared statement - 虽然不是100%保证防止SQL注入它是很多 很难这样做。 插入时使用准备好的声明的好处之一 多个记录 - 声明是prepared一次和占位符是 绑定到变量名 - 然后在循环中多次执行很少 数据库服务器的额外开销。

$i=0;
$results = array();
$listno = !empty( $_REQUEST['listno'] ) ? $_REQUEST['listno'] : false;
$steps = !empty( $_POST['step'] ) ? $_POST['step'] : false;

if( $listno && $steps ){

    /* create simple insert statement */
    $sql='insert into `checkliststeps' (`listno`,`step`) values ( ?, ? );

    /* create a prepared statement to be executed multiple times */
    $stmt=$link->prepare( $sql );

    /* If there was an error, bail out */
    if( !$stmt ) exit( 'Failed to prepare sql query' );

    /* bind placeholders to variables - assumed integer and string from error message above */
    $stmt->bind_param('is', $listno, $step );

    /* iterate through data and execute multiple times */
    foreach( $steps as $step ) {
        $results=$stmt->execute();

        /* if the query failed, add to output array */
        if( !$result ) $results[]=$result;

        $i++;
    }

    /* close objects */
    $stmt->close();
    $link->close();
}

/* output message */
if( !empty( $results ) ){
    printf( 'Some errors occurred during processing. A total of %d / %d failed.', count( $results ), $i );
} else {
    printf( '%d records added successfully.', $i );
}

登录后方可回帖

Loading...