Different query result in PHP and mySQL interactive queries.

[SheenLim08]

Hi PHP enthusiast,

I hope you are having a wonderful day, I am learning PHP as a hobby and learning the ways of PHP talking to SQL.

I have the following PHP code.

login.php
$mysqlDSN       = "mysql:host=127.0.0.1;dbname=mysql_tutorial;port=3306;charset=utf8";
$mySQLUsername  = "phpuser";
$mySQLUserPass  = "Welcome123!";

phpsearchresult.php

if (filter_has_var(INPUT_POST, 'txtISBN')) {
            try {
                require_once './login.php';
                
                $sqlConnection = new PDO($mysqlDSN, $mySQLUsername, $mySQLUserPass);
                $sqlConnection->
                $sqlStatement = $sqlConnection->prepare("SELECT * FROM classics WHERE isbn = :isbnSearchID");
                $parameters = [
                    ":isbnSearchID" => sanitiseForSQL($sqlConnection, filter_input(INPUT_POST, 'txtISBN'))
                ];
                
                if ($sqlStatement->execute($parameters)) {
                    $result = $sqlStatement->fetch(PDO::FETCH_ASSOC);
                    
                    if ($result) {
                        printf("<br />");
                        printf("ISBN: "     . sanitiseForHTML($result['isbn'])      . "<br />");
                        printf("Author: "   . sanitiseForHTML($result['author'])    . "<br />");
                        printf("Book: "     . sanitiseForHTML($result['title'])     . "<br />");
                        printf("Category: " . sanitiseForHTML($result['category'])  . "<br />");
                        printf("Year: "     . sanitiseForHTML($result['year'])      . "<br />");
                        printf("Type: "     . sanitiseForHTML($result['type'])      . "<br />");
                    }

                    else {
                        printf("<p>The ISBN you are looking for has not been found!</p>");
                        printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>");
                    }

                } else {
                    printf("<p>There was an issue during the search query!</p>");
                    printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>");

                }
            } catch (PDOException $ex){
                printf("<p>" . $ex->getMessage() . "</p>");

            } catch (Exception $ex) {
                printf("<p>" . $ex->getMessage() . "</p>");

            } finally {
                $result             = null;
                $sqlStatement       = null;
                $sqlConnection      = null;
            }
        }
        
        function sanitiseForSQL(PDO $sqlConnection, string $variable) : string {
            $returnValue = stripslashes($variable);
            return $sqlConnection->quote($returnValue);
        }
        
        function sanitiseForHTML($variable) : string {
            $returnValue = htmlentities($variable);
            return $returnValue;
        }

}

 

The line that is saying "if ($result) {" is returning false when I'm expecting it to return true.

Once a is redirected (via form post) to phpsearchresult.php, the variable txtISBN has a value of 9780099535974.

However if I manually query enter the following in a mySQL cli.

SELECT * FROM classics WHERE isbn = '9780099535974'

I am getting good results, is there any wrong with how I add the value for the named placeholder?

 

 

[Barand]

The whole point of using prepared statements with parameters is to avoid having to sanitize your inputs against SQL injection.

Adding some attributes to your PDO connection also helps.

• ATTR_EMULATE_PREPARES - this is "true" by default so set it "false" to stop it emulating prepared statements
• ATTR_ERRMODE - set this so that exceptions are thrown automatically and you don't have to keep checking for errors at every step
• ATTR_DEFAULT_FETCH_MODE - set this so you don't have to specify every time

Why are you using "printf()" when you aren't formatting anything? Simple "echo" would suffice.

1 hour ago, SheenLim08 said:

$sqlConnection->

that 3rd line in your try block might be causing problems.

Try a simpler approach...

require_once './login.php';
if ($_SERVER['REQUEST_METHOD']=='POST' && isset($_POST['txtISBN']) ) {
    $sqlConnection = new PDO($mysqlDSN, $mySQLUsername, $mySQLUserPass);
    // SET PDO ATTRIBUTES AFTER CONNECTING
    $sqlConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sqlConnection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $sqlConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    
    $sqlStatement = $sqlConnection->prepare("SELECT * FROM classics WHERE isbn = :isbnSearchID");
    $parameters = [
        ":isbnSearchID" => $_POST['txtISBN')
    ];
    
    if ($sqlStatement->execute($parameters)) {
        $result = $sqlStatement->fetch(PDO::FETCH_ASSOC);
        
        if ($result) {
            printf("<br />");
            printf("ISBN: "     . sanitiseForHTML($result['isbn'])      . "<br />");
            printf("Author: "   . sanitiseForHTML($result['author'])    . "<br />");
            printf("Book: "     . sanitiseForHTML($result['title'])     . "<br />");
            printf("Category: " . sanitiseForHTML($result['category'])  . "<br />");
            printf("Year: "     . sanitiseForHTML($result['year'])      . "<br />");
            printf("Type: "     . sanitiseForHTML($result['type'])      . "<br />");
        }

        else {
            printf("<p>The ISBN you are looking for has not been found!</p>");
            printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>");
        }

    } else {
        printf("<p>There was an issue during the search query!</p>");
        printf("<a href=\"phpsearchbook.php\">Go back to Search page.</a>");

    }
        

}

 

[SheenLim08]

Thanks Barand, adding the attributes worked for me.

I also searched the PDO->setattribute on https://www.php.net/manual/en/pdo.setattribute.php on the PHP manual to have a better understanding about the attribute values.

Thanks for your help.