Why is my SQL query in PHP failing?

Question

I have a PHP function that should return a database query:

function editprojectform(){
  global $conn;

  if(isset($_POST['project_id'])){
    $project_id = $_POST['project_id']; 
    $sql = "SELECT * FROM projects WHERE Project_ID=".$project_id;
      if ($conn->query($sql) === TRUE) {
        echo "It works";
      } else {
          echo "Error: " . $sql . "<br>" . $conn->error;
      }
  }
}

However it is printing out the error instead of printing out the project name:

Error: SELECT * FROM projects WHERE Project_ID=3

I can copy and paste this exact query into PHPadmin and it returns the expected result. I am trying to work out why it is not working here? I have a very similar function that deletes a record and it works fine.

Thank-you.

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Apr 15 '17 at 23:58
Most probably you are passing the id as a string. So try changing your query to this: `$sql = "SELECT * FROM projects WHERE Project_ID='".$project_id."'";` to wrap the string into single quotes. — Yolo, Apr 16 '17 at 00:05
It was because it wasn't passed as a string, weirdly it I have a query in a function that doesn't pass it as a string that works `"DELETE FROM projects WHERE Project_ID=".$project_id;` ... — Ginger Squirrel, Apr 16 '17 at 00:09
You could verify if it would work by trying the plain query `$sql = "SELECT * FROM projects WHERE Project_ID=3";`. If that works you can be sure that $project_id is a string. You could cast the value as INT as well when setting $project_id: `$project_id = (int)$_POST['project_id'];` — Yolo, Apr 16 '17 at 00:15
I did a bit more investigating which led me to trying my original code again and I have it working by using `if ($conn->query($sql)->num_rows > 0)`. — Ginger Squirrel, Apr 16 '17 at 00:44

score 3 · Accepted Answer · edited May 23 '17 at 12:09

I'm assuming you are using mysqli::query. If you read the documentation, particularly the return values section, it states that

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

Therefore, since you are using a SELECT statement, the function will either return FALSE or an object which both are not identical (===) to TRUE. So, it will always fail.

Unlike a SELECT statement, if you use a DELETE statement, the function will only return TRUE or FALSE. So depending on what type of statement you have, the function will return different values.

Simply follow the example in the documentation and replace your IF statement with if ($result = $conn->query($sql)) {.

score 1 · Answer 2 · answered Apr 16 '17 at 09:56

1

In this case, it would be better to use PDO.

$db = $pdo->prepare('SELECT FROM projects WHERE Project_ID=:Project_ID');
$db->bindParam(':Project_ID', $project_id);
$db->execute();
$res = $db->fetchAll();

var_dump($res)

answered Apr 16 '17 at 09:56

Doublemind

61
3

Why is my SQL query in PHP failing?

2 Answers2