SQL MariaDB syntax error

[adamherbstff]

Hello! So I am trying to get a feature of my code working where I check if the username/email is already taken. I am hitting a roadblock though with a weird error about syntax. But another query with almost the same syntax works fine! Here is the error:

) Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(username, email) VALUES('test','123@45.com')' at line 1 in C:\xampp\htdocs\Refactored\lib\DatabaseHelper.class.php on line 27

and

( ! ) PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(username, email) VALUES('test','123@45.com')' at line 1 in C:\xampp\htdocs\Refactored\lib\DatabaseHelper.class.php on line 27

This is my code for that function, everything else is working fine:

public function createUser($user) {

    $sql = "SELECT * FROM users(username, email) VALUES(?,?)";

    $statement = DatabaseHelper::runQuery($this->connection, $sql, Array($user->getUsername(), $user->getEmail()));

    $row = $statement->fetch();

    if($row->rowCount() > 0) {

        if ($username == $row['username']) {

            echo "Username exists";

        } elseif ($email == $row['email']) {

            echo "Email exists";

        }       

    } else {

        $hashed_password = password_hash($user->getPassword(), PASSWORD_DEFAULT);

        $sql = "INSERT INTO users(username, password, email) VALUES(?,?,?)";

        $statement = DatabaseHelper::runQuery($this->connection, $sql, Array($user->getUsername(), $hashed_password, $user->getEmail()));

    }

    }

I think this is the problem:

$sql = "SELECT * FROM users(username, email) VALUES(?,?)";

But it looks correct and I feel the rest of my code is correct. I do not know what MariaDB is but maybe my database is hitting a problem?

Thanks in advance for any help!

[Pepster64]

You have to look at your DatabaseHelper class " \lib\DatabaseHelper.class.php on line 27"

[adamherbstff]

I know I looked there and other functions use it just fine, but here is that code:

public static function runQuery($pdo, $sql, $parameters=array())     {

        // Ensure parameters are in an array

        if (!is_array($parameters)) {

            $parameters = array($parameters);

        }

        $statement = null;

        if (count($parameters) > 0) {

            // Use a prepared statement if parameters 

            $statement = $pdo->prepare($sql);

            $executedOk = $statement->execute($parameters);

            if (! $executedOk) {

                throw new PDOException;

            }

        } else {

            // Execute a normal query     

            $statement = $pdo->query($sql); 

            if (!$statement) {

                throw new PDOException;

            }

        }

        return $statement;

    }

This is line 27:
$executedOk = $statement->execute($parameters);

[r937]

@adamherbstff

I think this is the problem:

$sql = "SELECT * FROM users(username, email) VALUES(?,?)";

you are correct, that is indeed the problem

i don’t do php, but try this –

SELECT 'oh, hai' 
  FROM users
 WHERE username = ?
   AND email = ?

[adamherbstff]

Got it! Thank you!

[benanamen]

@adamherbstff

I check if the username/email is already taken

You are already off to a bad start. Do not do this. You are building in a Race Condition. Rather, put a unique constraint on the username and email fields, attempt the insert and capture the duplicate error if any and handle however you want.

[r937]

@benanamen

Rather, put a unique constraint on the username and email fields, attempt the insert and capture the duplicate error if any and handle however you want.

this is the correct answer

however, i should just like to point out how exceedingly unlikely that race condition will be – two users, each vying to establish the same userid, or the same email, at almost exactly the same time?

as Charlie Chan used to say, “i don’t think so, no”

[adamherbstff]

I do have them as a unique constraint in the database. I just wanted my own error to show up instead of it erroring from phpmyadmin. Also yes, I used an OR instead of an AND:

$sql = "SELECT * FROM users WHERE username=? OR email=?";

thanks!

[r937]

you’re letting users use phpmyadmin and you’re trying to hide a mysql error message from them?

[adamherbstff]

Sorry, I’m not sure what you are referring to. I think I’m protected against sql injection if thats what you are referring to. Thats not all my code. Care to elaborate for me? - thanks

[r937]

you said phpmyadmin

did you mean php?

because phpmyadmin is a piece of crap ('scuse my french) that should never be given to users

php is really good at trapping mysql error conditions and letting you construct your own error messages – there’s no need for a separate SELECT before your INSERT

[adamherbstff]

No so i put “unique” constraints on my username and email fields in phpmyadmin database. If I tried to register a new user i did not want duplicate username/emails. But if I did try to register a duplicate user the phpmyadmin error would show about the unique constraints. I’m just doiubling up secruity and handling an error more gracefully then the phpmyadmin error showing. AT least thats my logic behind it. - Is it really hurting anything?

Thanks