What does the "Unrecognised SQL statement" mean when I am trying to use IF NOT EXISTS?

Question

I am getting the error on line 26 as shown by my browser.

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "tut";

$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
    die("Database connection failed: ".mysqli_connect_error());
}
 if (isset($_POST['register']))
{
    $user = $_POST['username'];
    $pass = $_POST['password'];
    $pass2=$_POST['password1'];

    if(empty($username)||empty ($password)||empty($password1)){
        echo "Oops! Can't leave any field blank";
    }
    elseif($pass!=$pass2){
        echo "Passwords don't match";
    }   
    else{
        $phash = sha1(sha1($pass."salt")."salt");
        $sql=IF NOT EXISTS (SELECT * FROM users WHERE username = '$user')
            INSERT INTO users (id, username, password) VALUES ('', '$user', '$phash')
        ELSE
            RAISERROR 'Username exists, please select a different one';
        $result = mysqli_query($conn, $sql);
    }
}
?>

Is this not a correct way of writing the IF NOT EXISTS statement. Also when I try to execute this directly in XAMPP I get Unrecognised SQL statement error!

+1 to answer from @maytham-ɯɐɥʇʎɐɯ. P.S. please learn to use parameterized queries, because your code is vulnerable to SQL injection. — Bill Karwin, May 28 '17 at 18:10
Yes I have solved the issue, and I have understood my mistake I was combining SQL wid PHP code ..thanks for the help , I didn't do it exactly as you said , but I used what you meant!! @maytham-ɯɐɥʇʎɐɯ — lmgguy, Jun 08 '17 at 06:11

maytham-ɯɐɥʇʎɐɯ · Accepted Answer · 2017-05-28T20:18:27.300

This is how to do it, I have test it and it works:

$sql = "
    INSERT INTO users (username, password)
    SELECT * FROM (SELECT '$user', '$phash') AS tmp
    WHERE NOT EXISTS (
        SELECT username FROM users WHERE username = '$user'
    ) LIMIT 1;
";

This solution is inspired from this answer.

The problem is that you can not combine PHP and MySQL statement like you did, you need to encapsulate all MySQL statements in quote ".

What comes RAISERROR, it is not MySQL function, it belongs to Microsoft.

You could easily make php if statement that checks if $sql contain valid username and return your message. That part is left to your fantasy.

XAMPP has no thing to do with the error, it just a software that provides an Apache and MySQL installation for Windows.

Note: P.S. please learn to use parameterized queries, because your code is vulnerable to SQL injection. thanks to @BillKarwin for mentioning this.

What does the "Unrecognised SQL statement" mean when I am trying to use IF NOT EXISTS?

1 Answers1