8 Replies - 5813 Views - Last Post: 12 June 2010 - 12:49 AM Rate Topic: -----

#1 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 65
  • Joined: 29-April 09

[SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 01:39 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: [SOLVED] PDO query problem. Issue with limit

#2 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 65
  • Joined: 29-April 09

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 01:55 PM

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;

        }


Was This Post Helpful? 4
  • +
  • -

#3 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,437
  • Joined: 23-August 08

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 02:05 PM

I marked it as solved for you. We don't have a SOLVED button per se. :)
Was This Post Helpful? 0
  • +
  • -

#4 KingCuddles  Icon User is offline

  • D.I.C Regular

Reputation: 176
  • View blog
  • Posts: 496
  • Joined: 20-December 08

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 02:13 PM

samuraitux Thanks for posting how you fixed it. :)
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3490
  • View blog
  • Posts: 10,058
  • Joined: 08-June 10

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 04:17 PM

you may want to look into PDOStatement->bindValue() also, that doesn’t create a reference to the PHP variable.
Was This Post Helpful? 0
  • +
  • -

#6 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 65
  • Joined: 29-April 09

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 04:34 PM

@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

Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,437
  • Joined: 23-August 08

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 05:44 PM

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.
Was This Post Helpful? 1
  • +
  • -

#8 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 65
  • Joined: 29-April 09

Re: [SOLVED] PDO query problem. Issue with limit

Posted 11 June 2010 - 08:24 PM

View PostJackOfAllTrades, 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).
Was This Post Helpful? 1
  • +
  • -

#9 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3490
  • View blog
  • Posts: 10,058
  • Joined: 08-June 10

Re: [SOLVED] PDO query problem. Issue with limit

Posted 12 June 2010 - 12:49 AM

View Postsamuraitux, 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).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1