Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    4

    Search with 4 fields


    I'm trying to build a query from a maximum of 4 fields, where some of the fields may or may not be used. I tried to adapt the search I used to use, but I'm unsure how to get it into the proper PDO format, thanks.
    PHP Code:
    // The fields come from 4 drop menus on the previous page and are being relay properly
    $ad_pstate $_POST['ad_pstate'];
    $ad_dob $_POST['ad_dob'];
    $ad_race $_POST['ad_race'];
    $ad_hair $_POST['ad_hair'];
    $ad_eyes $_POST['ad_eyes'];

    // set age range
    if ($ad_dob == 1) {
      
    $age1 '18';
      
    $age2 '20+1';
    }

    if (
    $ad_dob == 2) {
      
    $age1 '21';
      
    $age2 '30+1';
    }

    if (
    $ad_dob == 3) {
      
    $age1 '31';
      
    $age2 '40+1';
    }

    if (
    $ad_dob == 4) {
      
    $age1 '41';
      
    $age2 '50+1';
    }

    if (
    $ad_dob == 5) {
      
    $age1 '51';
      
    $age2 '99+1';
    }
     
    // build query based on user input
    $select "SELECT * from ads ";
    $where "WHERE ad_pstate = $ad_pstate ";

    // determine which fields were used
    if (strlen($ad_dob)) {
      
    $where .= "AND ad_dob BETWEEN DATE_ADD(CURDATE(), INTERVAL -{$age1} YEAR) 
                    AND DATE_ADD(CURDATE(), INTERVAL -
    {$age2} YEAR) ";
    }

    if (
    strlen($ad_race)) {
      
    $where .= "AND ad_race = '".$ad_race."' ";
    }

    if (
    strlen($ad_hair)) {
      
    $where .= "AND ad_hair = '".$ad_hair."' ";
    }

    if (
    strlen($ad_eyes)) {
      
    $where .= "AND ad_eyes = '".$ad_eyes."' ";
    }

    // finish query
    $groupby " group by ad_name";
    $orderby " order by ad_name asc";

    // finished query
    $query $select.$where.$groupby.$orderby;

    // execute query
    $stmt $conn->query('$query');
    $ads $stmt->rowCount(); 
    I am getting this error...
    Code:
    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$query'
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,632
    Rep Power
    1811
    The answer is in the question, I think!
    Where you actually do the query ($stmt = $conn->query('$query'); ) the use of apostrophes is telling php not to expand the variables within, change to "$query" and that should fix that issue.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    Don't know about the query statement itself, but your inclusion of single quotes around $query in the call is wrong. You are trying to execute a query statement that is literally the characters "$query", not the complex statement you spent so much time building.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1017
    A warning for other readers: Do not use this code as a template. It's wide open to SQL injections and would give anybody direct access to your database. He wants it like that, but you probably not.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    4
    Thank you, that removed the error msg, but the actual query doesn't work and am not sure as to why :/

    I have the file echoing the query to the page so I can see it, but it's not working. This is a sample of a query
    Code:
    Query: "SELECT * from ads WHERE ad_pstate = 'Kentucky' AND ad_dob BETWEEN DATE_ADD(CURDATE(), INTERVAL -21 YEAR) AND DATE_ADD(CURDATE(), INTERVAL -30+1 YEAR) AND ad_race = 'Black' AND ad_hair = 'Black' AND ad_eyes = 'Brown' group by ad_name order by ad_name asc";
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    50
    Rep Power
    4
    Changed my mind, this person isn't worth my time or effort.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    I have to guess that all that date manipulation is the problem, if your other criteria are known to exist in the db. Why don't you generate the date criteria outside the query and simplify the statement that way?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    Originally Posted by RodneyB
    Changed my mind, this person isn't worth my time or effort.
    To whom are you referring? It would be nice to know so as not to tick off others who may be helpful.

    Comments on this post

    • Jacques1 disagrees : Helpful with what? Trashing his server? Yeah, if that's your goal, you're doing an excellent job.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    Rodney - not everyone here is a pia so I hope you continue to post.

    Jacques1 - You can be such a miserable person. Just because you are the self-appointed omniscient on this forum (among other more-helpful members!) doesn't mean you have to trash everyone's attempts at being helpful.

    As for Rodney's vulnerability - I don't see it, which I'm sure you will find fault with as well. As stated the inputs are from dropdown menus so I fail to see why he must handle them any differently. I'm sure that will merit me some negative credit from you. As for the rest of the code I also don't see any security breaches there either.

    So Jacques1, if you're having a bad day, please go have it elsewhere.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,350
    Rep Power
    630
    Not that Jacques1 needs anyone to defend him but if you can't see the obvious vulnerability in the OPs original code I would urge you to refrain from providing advice to anyone until you gain more experience yourself, at least in the area of security.

    Yes, Jacques1 can be a bit harsh at times but I can understand his frustration as OPs ask for help then repeatedly ignore or disregard advice and try to defend bad code. I have fallen into that same trap myself as have most regular posters here. I have at times asked myself why I bother but this forum has provided so much help to me (especially back in my early days) that I feel obligated to give back.

    It seems that we have all become too thin skinned in recent times and people seem to take offense at virtually anything.

    Comments on this post

    • Jacques1 agrees
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    As I said - I fail to see any vulnerability since the POST inputs are supposedly from the OP's dropdown menu selections, and not uncontrolled user input. Care to explain?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,350
    Rep Power
    630
    Not the OPs selections but whatever is in $_POST returned by the submit. Thus it is all under user control. As I said you need to refrain but giving advice until you have more experience with security. One can easily change the items in the drop down menu to inject any SQL query into that code.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    189
    Rep Power
    0
    How does a user change the values in a drop down menu?
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    538
    Originally Posted by jimmyg999
    How does a user change the values in a drop down menu?
    Why does there have to be a drop down menu in the first place? One simple post using curl to the unprotected site, and the database is compromised. And if you want an example of how a traditional web-browser user could change the value, look at firebug.

    Comments on this post

    • gw1500se agrees : It doesn't but I was just trying to explain it in terms a novice might understand. Jacques1 did a more elaborate explaination.
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1017
    To expand on this a bit:

    When you visit a website with your browser, what actually happens is this: Your browser assembles an HTTP(S) request and sends it to the server. Then the server processes the request, assembles an HTTP(S) response and sends it back to you. Finally, the browser will render the response and show you a nice HTML page or whatever.

    So in reality, it's just two IP addresses exchanging bytes. The browser is completely irrelevant for this. It's just a graphical interface to make the data human-readable. If I want to, I can write down the raw HTTP(S) messages myself and not use a browser at all. Nothing prevents me from doing that.

    The “restrictions” you may have put into your HTML form don't mean a thing. All that does is kindly ask the user for certain input. Whether or not the user actually abides by your rules is completely up to them. Anybody can send any data to any public server.

    I repeat: Anybody can send anything to your server. If you blindly trust the input and insert it straight into your queries, you're in deep trouble. The code above is effectively a database backdoor. By manipulating the query, it's possible to fetch arbitrary data or even take over the server. And it's not even difficult. Any script kiddie with basic SQL knowledge or a tool like sqlmap can do it.

    Do you understand now why I have a problem with your advice? I know that you're just trying to help. But well-intentioned is not the same as well-done.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo