CODEWITHSUNDEEP
phppdo
dscrown
dscrown

Reputation: 578

How do you stop injection in this PHP/PDO

So I have look at so many post, web sites and video and now I am so confused! I can't seem to get it right. How do you stop injection in this PHP/PDO. I have this code that works, but it allows injection. 

//*THIS WORKS BUT ALLOWS INJECTION
//*

//The variable $word comes from another php file where the search is created.
public function getAllCards($word) {

    $sql = "SELECT * FROM carddbtable WHERE businessNameDB='".$word."'";

foreach ($this->conn->query($sql) as $row) {

    echo json_encode($row)."<br>"."<br>";
}

$db = null;
}

With this new code I am trying to remove the variable "$word" from the "SELECT * FROM " statement to stop the injection and add the "prepare" and the error checking and the "execute" statement, but I can't get it right. How would I do this? FYI this is a GoDaddy shared server.

//Getting the search "word" from the GetCards.php
 public function getAllCards($word) {

    //Empty var to store all returned info from db
    $returnArray = array();


    // sql statement to be executed 
    $sql = "SELECT * FROM carddbtable WHERE businessNameDB=':word";

    // prepare to be executed 
    $statement = $this->conn->prepare($sql);


    // error occurred
    if (!$statement) {
        throw new Exception($statement->error);
    }


    // execute statement
    $statement->execute( :word => '$word' );


//run the query
foreach ($this->conn->query($statement) as $row) {

echo json_encode($row)."<br>"."<br>";

}

    // store all appended $rows in $returnArray to be sent to app
    $returnArray[] = $row;   
}

Upvotes: 0

Views: 93

Answers (3)

dscrown
dscrown

Reputation: 578

I got this working

//*FUNCTION TO GET CARD FROM SEARCH WORD CALLED FROM GetCards.php   
public function getAllCards($word) {

//Connect to db using the PDO not PHP
$db = new PDO('mysql:host=localhost;dbname=xxxx', 'xxxx', 'xxxx');

//Here we prepare the SELECT statement from the search word place holder :word
$sql = $db->prepare('SELECT * FROM carddbtable WHERE businessNameDB=:word');

//We execute the $sql with the search word variable"$word"
$sql->execute([':word' => $word]);

//Looping through the results
foreach ($sql as $row)

//Print to screen
echo json_encode($row). "<br>"."<br>";
}

Upvotes: 0

dscrown
dscrown

Reputation: 578

This is what i have now. 

  //Getting the search "word" from the GetCards.php
 public function getAllCards($word) {

    //Empty var to store all returned info from db
    $returnArray = array();


    //  prepare to be executed sql statement to be executed if not entered word
    $statement = $this->conn->prepare("SELECT * FROM carddbtable WHERE businessNameDB=:word");



    // error occurred
//        if (!$statement) {
//           throw new Exception($statement->error);

//        }


    // execute statement
    $res = $statement->execute([ 'word' => $word ]);


//run the query
foreach ($this->conn->query($res) as $row) {

echo json_encode($row)."<br>"."<br>";

}

    // store all appended $rows in $returnArray to be sent to app
    $returnArray[] = $row;   

}

Upvotes: 0

tadman
tadman

Reputation: 211670

You've almost got it. PDO, like many database drivers, will be responsible for all of the escaping, so just leave the placeholder as plain as possible:

$sql = "SELECT * FROM carddbtable WHERE businessNameDB=:word";

No ' necessary there.

Now when you execute() a PDO statement you get a result which you need to capture into a variable:

$res = $statement->execute([ 'word' => $word ]);

As Ibu and chris85 point out the '$word' part is also incorrect. Avoid quoting single variables, it's not only pointless, it can cause trouble, like here where you're binding to literally dollar-sign word, not the value in question. This goes doubly for "$word".

Then you fetch from that. Right now you're calling query() on the statement, which is incorrect.

Another thing to note is kicking the habit of making throw-away variables like $sql as these are just junk. Instead pass the argument directly:

$statement = $this->conn->prepare("SELECT * FROM carddbtable WHERE businessNameDB=:word");

This avoids accidentally mixing up $sql3 with $sql8 if you're juggling a bunch of these things.

Upvotes: 4

Related Questions