Having trouble querying the database with a String with PDO::query()

Question

I'm using a PDO driver to connect to a mySQL database. I have had luck querying this database when I search by ID; however, when I try to change the query from ID to first_name, I just get an error. Below is my code:

This code works

//functions.php file

function query_database($id) {
    include("connection.php");

    try {
        $results = $db->query("SELECT first_name, last_name FROM master_tradesmen WHERE id = $id");
        }catch(Exception $e) {
            echo "Something went wrong with the query.";
        }

        $data = $results->fetch();
        return $data;
    }

    $res = query_database(1);
    print_r($res);

This returns:

Array ( [first_name] => Steve [0] => Steve [last_name] => Albertsen [1] => Albertsen )

So now, instead of searching by ID, I just want to search by first name. Here is the slightly altered code:

function query_database($name) {
    include("connection.php");

    try {
        $results = $db->query("SELECT first_name, last_name FROM master_tradesmen WHERE first_name = $name");
        }catch(Exception $e) {
            echo "Something went wrong with the query.";
        }

        $data = $results->fetch();
        return $data;
    }

    $res = query_database('Steve');
    print_r($res);

This returns the following error:

Notice: Undefined variable: results in /home/locompre/public_html/php/functions.php on line 12

Fatal error: Uncaught Error: Call to a member function fetch() on null in /home/locompre/public_html/php/functions.php:12 Stack trace: #0 /home/locompre/public_html/php/functions.php(16): query_database('Steve') #1 {main} thrown in /home/locompre/public_html/php/functions.php on line 12

Any insight into why this might be happening?

since you're using pdo, why not use prepared statements instead — Kevin, Feb 27 '20 at 02:25
https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection — Dima Tisnek, Feb 27 '20 at 04:43
The core reason is that `$id` is probably numeric, resulting in `WHERE id = 42` and `$name` is probably a string, resulting in `WHERE name = Alexa Siri the 3rd` which doesn't parse. Then you get an exception, `echo` is lost somewhere (buffered?) and `$results` is never set. Please parametrise your query properly `WHERE x = ?` and then pass those parameters, so that they can be converted correctly. — Dima Tisnek, Feb 27 '20 at 04:45
Does this answer your question? [How to bind parameters in mysql query?](https://stackoverflow.com/questions/38129675/how-to-bind-parameters-in-mysql-query) — Dima Tisnek, Feb 27 '20 at 04:47
Does this answer your question? [How to include a PHP variable inside a MySQL statement](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) — Dharman, Feb 27 '20 at 09:22

CodyKL · Accepted Answer · 2020-02-27T02:40:24.083

You're open for SQL injections! The error happens because you initialize the variable result inside the try block. The variable isn't accessible outside the try-catch block if a error occurs, which is fact because strings need to be wrapped with single quotes in a SQL query!.

A better way to do it is:

function query_database($name) {
    include("connection.php");
    try {
        $stmt = $db->prepare("SELECT first_name, last_name FROM master_tradesmen WHERE first_name = :name");
        $stmt->bindValue(':name', $name);
        $stmt->execute();

        $data = $stmt->fetchAll();
        return $data;
    }catch(Exception $e) {
        echo "Something went wrong with the query.\n<br>".
            "Error: \n<br>" . $e->getMessage() . "\n<br>" . 
            "StackTrace: \n<br>"  . $e->getTraceAsString();
        return null;
    }

}

$res = query_database('Steve');
print_r($res);

Having trouble querying the database with a String with PDO::query()

1 Answers1