Preventing SQL Injection using parameterized queries

Question

I am trying to prevent sql injection and according to what I've been searching, the use of parameterized queries can help. Below is my PHP code on displaying the books saved in the database basing on its status which is an integer (0 for unavailable and 1 for available):

public function getBooksByStatus(){
        $qry=$this->stmt_init();
        if($qry->prepare("SELECT bookID, title, status FROM books WHERE status= ? ORDER By title ASC")){
            $qry->bind_param("i",$_GET[id]);   //$_GET[id] refers to the status
            $qry->execute();
            echo json_encode($qry->fetch_all(MYSQL_ASSOC));
            return;
    }
}

After testing the code, it doesn't have any error but it doesn't return anything.

This was my code before without parameterizing queries and actually, it works:

$qry = $this->query("SELECT bookID, title, status FROM books WHERE status= $_GET[id] ORDER By title ASC");
        echo json_encode($qry->fetch_all(MYSQL_ASSOC));
        return;

Can anyone help me, please?

What's behind $this->stmt_init? Or what class are you in ? — Clément Malet
– Clément Malet, Commented Jul 10, 2014 at 7:25
print_r( $qry->errorInfo() ) and paste the result, please. — ʰᵈˑ
– ʰᵈˑ, Commented Jul 10, 2014 at 8:05
It's the class where all functions used in the program are defined, including the function to connect to the database. — jmraz
– jmraz, Commented Jul 10, 2014 at 8:31
The return; means it will go back to where it was called, but it doesn't retrieve any rows from the database. I think the $qry->fetch_all(MYSQL_ASSOC) is the problem? I'm not sure.. — jmraz
– jmraz, Commented Jul 10, 2014 at 8:39

Community · Accepted Answer · 2023-11-17 20:13:27Z

0

I'm not sure how sensitive PHP is for things like this. However:

$qry->bind_param("i",$_GET[id]);

First off all I would use $_GET['id'] just to be pure. More importantly I would cast $_GET['id'] to integer since you use "i" which according to https://www.php.net/manual/pl/mysqli-stmt.bind-param.phpdocumentation means integer.

$_GET array always contains strings.

Last thing which makes me suprised is:

$qry=$this->stmt_init();

Why you use $this instead of some var name which contains new mysqli object. I guess it can have some logical explanation. Your class extends mysqli? or something? It seems like bad practice to me. However it shouldn't have impact to result.

My tip try cast $_GET['id'] to integer

That's all what I get. Hope this will help you.

edited Nov 17, 2023 at 20:13

CommunityBot

11 silver badge

answered Jul 10, 2014 at 8:03

zimi

1,62413 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

deceze Over a year ago

The value will be cast to an integer by the fact that he's binding it as 'i'. It's not necessary to additionally manually cast it.

jmraz Over a year ago

I tried replacing $_GET[id] to this $_GET['id'], but it still doesn't work.

Collectives™ on Stack Overflow

Preventing SQL Injection using parameterized queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related