dsbiw2911188
2015-07-23 14:05
浏览 3
已采纳

PHP mySQLi准备失败,重复列'?'

I am attempting to prepare a statement with mysqli

$stmt = $mysqli->prepare("INSERT HIGH_PRIORITY INTO `user` (`FirstName`, `LastName`, `Department`, `Email`) SELECT * FROM (SELECT ?,?,?,?) AS tmp WHERE NOT EXISTS ( SELECT `Email` FROM `user` WHERE `Email` = ? ) LIMIT 1;");
if (!$stmt) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$statementReturnCode = $stmt->bind_param("sssss", $ssoFirstName, $ssoLastName, $ssoDepartment, $ssoEmail, $ssoEmail);
if (!$statementReturnCode) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$stmt->execute();
$stmt->close();

When this is run I receive the following error:

errno: 1060, error: Duplicate column name '?'

I've been able to bind in this fashion in the past, but I've never tried to bind the same column twice in a different location in the query (Email).

How can I use the same value for Email in two different locations, or is this a different issue?

To clarify what is being done with this query:

This query will be run frequently. If the user exists already in the user table, no insert should be attempted. If the user does not exist, the user should be added to the user table.

The user table has a UserID field that auto-increments. If an insert is attempted the user will not be added due to a unique constraint, but the AUTO-INCREMENT will add 1 even though the insert did not occur. This WHERE NOT EXISTS query is an attempt to mitigate this issue.

Example use:

INSERT INTO `user` (
    `user`.`FirstName`, 
    `user`.`LastName`, 
    `user`.`Department`, 
    `user`.`Email`)
SELECT * FROM (SELECT 'John', 'Doe', 'Marketing', 'John.Doe@mycorp.com') AS tmp
WHERE NOT EXISTS (
    SELECT `user`.`Email` 
    FROM `user` 
    WHERE `user`.`Email` = 'John.Doe@mycorp.com'
) LIMIT 1;

I have tested this query and it works as I had expected. The issue I'm having is with properly changing this query into a prepared statement with php.

图片转代码服务由CSDN问答提供 功能建议

我正在尝试用mysqli

  $准备一个语句 stmt = $ mysqli-> prepare(“INSERT HIGH_PRIORITY INTO`user`(`FirstName`,`LastName`,`Department`,`Email`)SELECT * FROM(SELECT?,?,?,?)AS tmp WHERE NOT  EXISTS(选择`Email` FROM`user` WHERE`Email` =?)LIMIT 1;“); 
if(!$ stmt){
 printf('errno:%d,错误:%s',$ mysqli-  > errno,$ mysqli->错误); 
 die; 
} 
 
 $ statementReturnCode = $ stmt-> bind_param(“sssss”,$ ssoFirstName,$ ssoLastName,$ ssoDepartment,$ ssoEmail,$  ssoEmail); 
if(!$ statementReturnCode){
 printf('errno:%d,错误:%s',$ stmt-> errno,$ stmt->错误); 
} 
 
 $  stmt-> execute(); 
 $ stmt-> close(); 
   
 
 

运行此命令后,我收到以下错误:

  errno:1060,错误:重复的列名'?'
   
 
 

我已经能够以这种方式绑定 过去,但我从未试图在不同的位置将同一列绑定两次 n在查询(电子邮件)中。

如何在两个不同的位置使用相同的电子邮件值,或者这是一个不同的问题?

澄清此查询正在执行的操作:

此查询将经常运行。 如果用户已存在于用户表中,则不应尝试插入。 如果用户不存在,则应将用户添加到用户表中。

用户表具有自动递增的UserID字段。 如果尝试插入,则由于唯一约束而不会添加用户,但即使插入未发生,AUTO-INCREMENT也将添加1。 这个WHERE NOT EXISTS查询试图缓解这个问题。

使用示例:

  INSERT INTO`user`(
  user``FirstName`,
`user`。LastName`,
`user` .Department`,
`user`。Email!)
SELECT * FROM(SELECT'John','Doe'  ,'营销','John.Doe @ mycorp.com')AS tmp 
WHERE NOT NOT EXISTS(
 SELECT`user`。Email` 
 FROM`user` 
 WHERE`user` .Email` ='  John.Doe@mycorp.com'
)LIMIT 1; 
   
 
 

我测试了这个查询,它按照我的预期工作。 我遇到的问题是将此查询正确地更改为使用php的预准备语句。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongqiabei7682 2016-06-29 11:40
    已采纳

    This cannot be done. Prepared statements using PHP's mysqli extension cannot be used for several things including:

    • Table names
    • Columns in select lists

    I was attempting to use a dynamic item in a select list which cannot be done.

    https://www.owasp.org/index.php/PHP_Security_Cheat_Sheet#Where_prepared_statements_do_not_work

    打赏 评论
  • dscc90150010 2015-07-23 14:12

    Column names aren't string literals, you don't bind column names

    $stmt = $mysqli->prepare(sprint("INSERT HIGH_PRIORITY INTO `user` (`FirstName`, `LastName`, `Department`, `Email`) SELECT * FROM (SELECT '%s', '%s', '%s', '%s') AS tmp WHERE NOT EXISTS ( SELECT `Email` FROM `user` WHERE `Email` = ? ) LIMIT 1;"), $ssoFirstName, $ssoLastName, $ssoDepartment, $ssoEmail);
    if (!$stmt) {
        printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
        die;
    }
    
    $statementReturnCode = $stmt->bind_param("s", $ssoEmail);
    if (!$statementReturnCode) {
        printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
    }
    
    打赏 评论

相关推荐 更多相似问题