[samuraitux]

Hello everyone. I have been working with this query and I can't seem to get it correct. The problem I have is when I replace LIMIT 20 with LIMIT ? nothing happens.

 public function getSummary($startDate, $endDate)
        {
                $sql = "SELECT firstname, lastname, DATE(signin) AS signin_date, TIME(signin) AS signin_time,
                        appttime, DATE(processtime) AS process_date, TIME(processtime) AS process_time,
                        categories.name FROM visitors inner join categories ON (visitors.dept = categories.id)
                        where DATE(signin) BETWEEN ? AND ? Limit ?";
                
                $limit = 30;
                $db = new Database();
                $stmt = $db->prepare($sql);
                $stmt->execute(array($startDate, $endDate, $limit));

                $this->results = $stmt->fetchAll(PDO::FETCH_NUM);
                return $this->results;

        }

can anyone tell me what in the world is going on. I am testing now but I plan on actually passing the limit in on the future.

[samuraitux]

Ok I figured it out. Funny I can figure this out be can't figure out how to mark a thread as solved. But anyways what I had to do was bind the param. Here is how I fixed it

public function getSummary($startDate, $endDate, $limit = 100)
        {
                $sql = "SELECT firstname, lastname, DATE(signin) AS signin_date, TIME(signin) AS signin_time,
                        appttime, DATE(processtime) AS process_date, TIME(processtime) AS process_time,
                        categories.name FROM visitors inner join categories ON (visitors.dept = categories.id)
                        where DATE(signin) BETWEEN :start AND :end Limit :limit";
                
                $db = new Database();
                $stmt = $db->prepare($sql);
                $stmt->bindParam(':start', $startDate);
                $stmt->bindParam(':end', $endDate);
                $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
                $stmt->execute();
                                
                $this->results = $stmt->fetchAll(PDO::FETCH_NUM);
                return $this->results;

        }

[JackOfAllTrades]

I marked it as solved for you. We don't have a SOLVED button per se.

[KingCuddles]

samuraitux Thanks for posting how you fixed it.

[Dormilich]

you may want to look into PDOStatement->bindValue() also, that doesn’t create a reference to the PHP variable.

[samuraitux]

@KingCuddles - no problem I just hope some sees this before they go nuts like I did. I think I spent probably 1.5 hours on this trying to get is solved then after posting here I went back to php.net site and low and behold found out about the bindParam method.

@Dormilich - I will certainly take a look at that.

One question for everyone. When doing the bindParam or bindValue does that also do escaping or are you still open to sql injesctions? As of now I have it so that when they click in the box they are given the jquery datepicker but I am sure they can backspace that and place something malicious in the box.

Normally I would use mysqli and while doing that I would use the mysql_real_escape_string function. But I am learning about PDO and I am enjoying it so I want to stick with PDO instead of going back to mysqli.

Thanks JackofAllTrades. When I clicked edit on the post I noticed it wouldn't allow me to change the title. Is there anyway I can do that so I don't have to bother the mods with this?

This post has been edited by samuraitux: 11 June 2010 - 04:36 PM

[JackOfAllTrades]

I don't rightly know the answer to your question for me. There's a period during which the post is available for editing, but I don't know if that's also true of the title. Seeing everything through a Mod's lens, I can't tell!

Again, thanks for telling us how you solved the problem, rather than deleting your post like far too many do.

[samuraitux]

JackOfAllTrades, on 11 June 2010 - 05:44 PM, said:

I don't rightly know the answer to your question for me. There's a period during which the post is available for editing, but I don't know if that's also true of the title. Seeing everything through a Mod's lens, I can't tell!

Again, thanks for telling us how you solved the problem, rather than deleting your post like far too many do.

No problem. Glad I could help.

Also for anyone else that is following; I did some research and it looks like bindValue and bindParam do have some builtin escaping but according to the websites and what I found in my programming php book it was still best to try and do some whitelist checking of your own. So for in my case I could check the length of the start and end date as I know that they will always be in the form of yyyy/mm/dd so I can say that that field should be 10 fields (that is including the /'s).

[Dormilich]

samuraitux, on 11 June 2010 - 11:34 PM, said:

One question for everyone. When doing the bindParam or bindValue does that also do escaping or are you still open to sql injesctions? As of now I have it so that when they click in the box they are given the jquery datepicker but I am sure they can backspace that and place something malicious in the box.

afaik, it does not escape anything, but you are safe of SQL Injection (because you used a Prepared Statement and that in itself is secure). However, since PDO only knows a limited number of data types (int, string, bool, null) your insert query may choke on invalid data (but that doesn’t affect security).