多个INSERTS并保持PDO准备的语句的安全性 [英] multiple INSERTS and keeping PDO prepared statement security

问题描述

我有以下一段代码，向 mya_users 的每个成员发送一封邮件(这是将INSERT插入收件箱的作用.

I have the following piece of code which sends to each member of mya_users a mail (this is what INSERT into inbox does.

$query_write_mass = "SELECT id FROM mya_users ORDER by artist_real_address ASC";
$result_write_mass = $db->prepare($query_write_mass);
$result_write_mass->execute();
while ( list($receiver_id) = $result_write_mass->fetch(PDO::FETCH_BOTH) ) { 

   $stmt = $db->prepare
     ("INSERT INTO inbox(folder_id, sender_id, sender_type, receiver_id, 
       receiver_type, title, message_body, time, date, flag, spam) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt->bindValue(1, 0, PDO::PARAM_INT);
        $stmt->bindValue(2, 0, PDO::PARAM_INT);
        $stmt->bindValue(3, 'x', PDO::PARAM_STR);
        $stmt->bindValue(4, $receiver_id, PDO::PARAM_INT);
        $stmt->bindValue(5, $receiver_type, PDO::PARAM_STR);
        $stmt->bindValue(6, $_POST['title'], PDO::PARAM_STR);
        $stmt->bindValue(7, $_POST['body'], PDO::PARAM_STR);
        $stmt->bindValue(8, date("G:i:s"), PDO::PARAM_STR);
        $stmt->bindValue(9, date("Y-m-d"), PDO::PARAM_STR);
        $stmt->bindValue(10, 'n', PDO::PARAM_STR);
        $stmt->bindValue(11, '', PDO::PARAM_STR);                                                                                                                                                                                                   

        $stmt->execute();   
 }

我想要保持PDO准备好的语句的安全性和转义的好处， 但一次插入10行，所以如果我有40k插入，我将受益于多值插入速度并保持较低的插入数量.

what I want is to keep the benefits of security and escaping of PDO prepared statements, BUT insert say 10 rows at a time, so if I have 40k inserts I would benefit of multiple value insert speed and keep the number of inserts low.

谢谢

推荐答案

首先，让我向您保证恒定值是绝对安全的.因此，您可以大大减少代码中绑定参数的数量

First, let me assure you that constant value is perfectly secure. So, you can dramatically reduce the number of bound parameters in your code

INSERT INTO inbox(folder_id, sender_id, sender_type, receiver_id, 
   receiver_type, title, message_body, dt, flag, spam) 
   VALUES (0, 0, 'x', ?, ?, ?, ?, NOW(), 'n', '')");

我也将两个字段date和time合并为一个dt，因为没有理由将它们分开，但是它可以让我们使用较短的代码.

I also combined two fields date and time into one dt, as there is no reason to have them separated, yet it can let us use shorter code.

现在您可以转到下一步-使用 INSERT .. SELECT 方法

And now you can turn to the next step - using INSERT .. SELECT approach

INSERT INTO inbox(folder_id, sender_id, sender_type, receiver_id, 
   receiver_type, title, message_body, dt, flag, spam) 
   SELECT 0, 0, 'x', id, ?, ?, ?, NOW(), 'n', ''
   FROM mya_users ORDER by artist_real_address ASC

并将您的数据仅绑定到其余三个变量！

and bind your data to only three remaining variables!

这篇关于多个INSERTS并保持PDO准备的语句的安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！