Mysql statement not working with multiple AND clauses

Question

So this sql is designed to return 1 random project from the projects table

$sql = "select title, id, user_id, priority from projects
where ( closed = 0 and priority between 7 and 9 AND  user_id=$user_id )
ORDER by rand(), priority
limit 1";

No matter how I rearrange them with parenthesis etc. the query returns invalid error like

Notice: Undefined variable: user_id in /var/www/html/producerswip/includes/functions.php on line 734

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 MySQL server version for the right syntax to use near ') ORDER by rand(), priority limit 1' at line 2 in /var/www/html/producerswip/includes/functions.php:737 Stack trace: #0 /var/www/html/producerswip/includes/functions.php(737): PDO->prepare('select title, i...') #1 /var/www/html/producerswip/index.php(72): getRandomProject() #2 {main} thrown in /var/www/html/producerswip/includes/functions.php on line 737

UPDATE This is a total face palm moment. I was so focused on the sql statement error I ignored the first line which actually states the error. Thanks @David and others who have their eyes open. I have also taken @David advice and changed my sql to the following which is less attractive to sql injection

$sql = "select title, id, user_id, priority from projects
where ( closed = ? and priority between ? and ? AND user_id=? )
ORDER by rand(), priority
limit 1";
$results = $pdo->prepare($sql) or die(mysqli_error($pdo));
$results->execute([0,7,9,$user_id]);

Possible duplicate of ["Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset" using PHP](https://stackoverflow.com/questions/4261133/notice-undefined-variable-notice-undefined-index-and-notice-undefined) — David, May 31 '19 at 13:49
@JNevill and David I totally didnt see that first line. Face Palm. You're right of course, I just delete my question now, or wait till one of you post the answer and select it? — Norman Bird, May 31 '19 at 13:54
It's recommended that you also refer to this question: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php The root problem here is the SQL injection vulnerability. You're not in control of the SQL syntax being executed. — David, May 31 '19 at 13:58
@David I have updated my code, thanks for the reminder on using prepared statements. — Norman Bird, May 31 '19 at 14:51

score 1 · Accepted Answer · answered May 31 '19 at 13:49

Looks like your $user_id variable is not defined, meaning your resulting query would become the following:

where ( closed = 0 and priority between 7 and 9 AND  user_id= )
ORDER by rand(), priority
limit 1

Since there is no value after the user_id=, the query is invalid.

Mysql statement not working with multiple AND clauses

1 Answers1