Skip to content
Advertisement

NEWID function causing error for prepared statement in MYSQL

I am trying to create a forgot password system, so I am using the NEWID() function to create a random code to be sent to the user’s email. I am using prepared statements to prevent SQL injections, but this still isn’t working:

$sql = mysqli_prepare($conn, "INSERT INTO verifyEmail (username, code) VALUES (?, NEWID())");
mysqli_stmt_bind_param($sql, "s", $user);
mysqli_stmt_execute($sql);

I am getting a warning on line two that the first parameter can’t be a boolean. (Thus, there is a syntax error in the SQL code). I know that the NEWID() function is the issue, since removing it fixes the warning. Does NEWID() not work inside prepared statements? If so, how should I go about doing this? Also, I am required to then run a SELECT statement to get what value the NEWID() function returned. Am I going about this all wrong?

Advertisement

Answer

NEWID() is a SQL Server function, that does not exist in MySQL. The equivalent would be UUID():

$sql = mysqli_prepare($conn, "INSERT INTO verifyEmail (username, code) VALUES (?, UUID())");
mysqli_stmt_bind_param($sql, "s", $user);
mysqli_stmt_execute($sql);

As explained in the documentation, UUID() gives you a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement