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.
This cannot be done. Prepared statements using PHP's mysqli extension cannot be used for several things including:
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
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);
}