PDO WHERE Condition Refuses to Fetch Rows

Question

I have a query:

//Connect to DB w/ PDO
$pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
$id = $_GET["id"];
$stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = ?", PDO::FETCH_ASSOC);
$stmt->bindParam(1, $id);
try{
    $stmt->execute();
}catch(PDOException $err){
    //some logging function
}
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
    //select column by key and use
    $FirstName = $result['Name'];
} 
?>

This is the output:

object(PDOStatement)#2 (1) { ["queryString"]=> string(44) "SELECT * FROM nv_hoa WHERE id = 0100782019-8" }

The ID is being filled from the $id variable.

  while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
  {
      $Name = $row['Name'];

But when try to get

$Name = row['Name'];

I get UNDEFINED VARIABLE: FIRSTNAME

When I run:

<?php var_dump($stmt) ?>

I get this:

object(PDOStatement)#2 (1) { ["queryString"]=> string(44) "SELECT * FROM nv_hoa WHERE id = 0100782019-8" }

What am I doing wrong here? It works in another file.

But ID WILL NOT work here even know it's in the query field.

You are using a WHERE clause in the file you say doesn't produce data. There may be data in the `nv_hoa` table, but are you sure your query would find it? — Tangentially Perpendicular, Sep 04 '21 at 22:48
Yes because $stmt = $pdo->query("SELECT * FROM nv_mortgage WHERE id = $id"); works in functions.php but it doesn't work for $stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = $id"); they're identical files doing identical things but it will NOT work for nv_hoa table. — themeowman, Sep 04 '21 at 22:53
That's not what I asked. If there is no data in `nv_hoa` that matches your `WHERE` condition it will return an empty result set — Tangentially Perpendicular, Sep 04 '21 at 22:55
If I go to http://mywebsite.com?id=09090 the functions_hoa.php gets the ?id= It does the same thing for functions.php and works fine. The condition refuses the acknowledge WHERE id = $id for the nv_hoa table. That's why I am here. I don't know what to make of it. I've even hardcoded it and got rid of the $_GET['id'] — themeowman, Sep 04 '21 at 23:02
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 05 '21 at 11:18
I updated my code. It is causing this issue sitll. I am at a loss. — themeowman, Sep 05 '21 at 14:26

score 0 · Answer 1 · answered Sep 04 '21 at 23:05

I can see you are fairly new to PHP and PDO. You have created a script that is vulnerable to SQL-injection,so we will fix that to.

It is important to understand what pdo is doing in order to understand what is going wrong. It will also make you understand the security problem you have created for yourself.

PDO makes it posible for the programmer to "compile" the SQL query before passing it the parameters and execute it. This has some benefits, mainly it is faster when you want to execute the same query multiple times(with different parameters) and it is much more secure.

Lets take your query as an example:

$stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = $id");

If I would manage to change the contents of $id to something like:

$id="1; SELECT * FROM users;"

The query you would execute would become:

SELECT * FROM nv_hoa WHERE id =1; SELECT * FROM users;

Which would result in you listing every user and password in the user table. This is a very well know and one of the most dangerous attacks out there. To counter this, we can use PDO's pre-compiled queries (a.k.a prepared statements)

Instead of:

$stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = $id");

Now use:

$stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = ?");

Pdo now compiled your query within the $stmt object. So now you can execute this query (as many times as you want) using the parameters you prefer.

$stmt->execute([$id]);

This executes the compilled query with the $id parameter. If the $id parameter contains SQL code, it will not become part of the query as you have already compiled the query, as so an SQL-Injection attack becomes near to impossible.

Now that your query is executed, you can fetch the results like:

while($row=$stmt->fetch()){ ... }

So in order to make your code work:

//PDO DB Connect to Fetch & Make into Vars from table nv_hoa
try {
  //Connect to DB w/ PDO
  $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
  $id = $_GET["id"];
  $stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = ?");
  $stmt->execute([$id]);
  while ($row = $stmt->fetch())
  {
      $Name = $row['Name'];
  }
} catch (PDOException $e) {
  print "Error!: " . $e->getMessage() . "<br/>";
  die();
}

Please do yourself(and others) a favor and research/learn a bit more before posting, you are making class-book mistakes that have been discussed and explained multiple times on pretty much every medium (including stackoverflow).

I read through and edit but get this Fatal error: Uncaught Error: Call to a member function execute() — themeowman, Sep 04 '21 at 23:26
I explained you the basics, it is up to you to write the code my friend. Read some tutorials, the web is full of them, read the php.net documentation and search on "php pdo" on this website, you will find tons and tons of info. https://www.phptutorial.net/php-pdo/ could be a good start. — Flip Vernooij, Sep 05 '21 at 04:28
Please just read though my updated post. It isn't a matter of knowing PDO right now. It's something small I am missing. I have explained the errors very well. — themeowman, Sep 05 '21 at 14:27

score -2 · Answer 2 · edited Sep 05 '21 at 18:48

-2

I solved the issue of a bool(false) output from a query even though the query dump had the ID.

Here is the fix.

The output of

$stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = ?", PDO::FETCH_ASSOC);

was

object(PDOStatement)#2 (1) { ["queryString"]=> string(44) "SELECT * FROM nv_hoa WHERE id = 0100782019-8" }

Which showed the ID was being passed properly. But the ID contained a special character '-' which caused the $stmt->execute([$id]); to break the page because $id was empty.

This was fixed as follows with single quotes.

  $stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = '$id'");
  $stmt->bindParam(1, $id);
  $stmt->execute();

I have found HUNDREDS of posts about this topic and this solves it. If you varaible has special chacters in a PDO statement you need to do this id = '$id'"

edited Sep 05 '21 at 18:48

Jean-François Fabre

137,073
23
153
219

answered Sep 05 '21 at 17:58

themeowman

31
3

I am sorry to say my friend but please take the time to read documentation, do a tutorial and at least put in some effort to read someones reply to your question. You clearly still haven't got a clue what you are doing and you are going against every piece of documentation out there and that includes my reply to you. – Flip Vernooij Sep 05 '21 at 20:27
This does not work and breaks the page. $id = $_GET["id"]; $stmt = $pdo->query("SELECT * FROM nv_hoa WHERE id = :id"); $statement->bindParam(':id', $id, PDO::PARAM_INT); $stmt->execute(); while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) – themeowman Sep 06 '21 at 00:55

PDO WHERE Condition Refuses to Fetch Rows

2 Answers2