Jump to content

Different query result in PHP and mySQL interactive queries.


SheenLim08

Recommended Posts

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?

 

 

Link to comment
Share on other sites

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>");

    }
        

}

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.