#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8

    Dynamic query based up selection


    Hello guys, I'm having a problem and hope that you can help me with this.

    A little background info.

    I use pdo and mysql. I have 1 database and 3 tables. A contact table, with contact info and most important a contact_id, an event table, with event name and event_id and a contact_event table, with contact_id and event_id (these will be filled when a contact has joined or participated an event, which means that the contact_event table will hold multiple of the same contact_id's but with a event_id).


    My situation is as followed.

    I need to make a selection from the database based upon checkboxes checked or text inputs filled.

    So a query must be made based upon checkboxes checked or text inputs. The checkboxes are mainly the events, so the query needs to check if the value (id) of the checkbox exists in the contact_event column, then needs to get the contact info linked to that event_id.

    The output should be something like (exported to csv, with headers of the column names for the contact info and event names if any returned):

    contact_id, contact info, event_name
    1, name address, 2(this is the id of the event).


    I think that the code should be something like:

    PHP Code:
    if (isset($_POST['checkbox'])){
         
    $query .= "WHERE ";
         foreach(
    $_POST['checkbox'] as $key => $value){
             
    $query .= "$key = $value";
         } 
    And for the text inputs in should be similar.

    Any thoughts on this? Am I doing this right?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    Any thoughts on this? Am I doing this right?
    Dangerous. You are at risk for SQL injection. Are your IDs integers? If so, typecast them as (int). You still have the problem with the column names. I would recommend not using the column names which are sent by the client, but hard code them in your server code. If so, then something like "id IN (1,2,3)" will work. Create a loop similar to what you did. If you really want to do it with the column names sent from the client (and, no you really don't want to), at least escape them and put the AND between each.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    Dangerous. You are at risk for SQL injection. Are your IDs integers? If so, typecast them as (int).
    Don't worry about the SQL injection, this is just part of the code, I use pdo with prepare, so that solves a lot already (maybe not everything).

    Originally Posted by NotionCommotion
    You still have the problem with the column names. I would recommend not using the column names which are sent by the client, but hard code them in your server code. If so, then something like "id IN (1,2,3)" will work. Create a loop similar to what you did. If you really want to do it with the column names sent from the client (and, no you really don't want to), at least escape them and put the AND between each.
    The column names are not in the client part (or send from the client part), they will be returned, after the query has run.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    Using PDO prepared statements does not protect against injection the way you are doing it.

    And, you are using the column names sent from the client when you use $key.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    Using PDO prepared statements does not protect against injection the way you are doing it.

    And, you are using the column names sent from the client when you use $key.
    Oh sorry, I made an error in the code I said as example.

    The checkboxes are not holding any column name, just the event name as value.
    The event names are hold in the events table and they will be used as header name.
    So the $key is not exactly used right.

    The only column names being used as header in the export to csv, is when getting the value from the SELECT.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    Try the following:

    PHP Code:
    if (isset($_POST['checkbox'])) {
     
    $query .= 'WHERE contact_id IN (';
     foreach(
    $_POST['checkbox'] as $value){ 
      
    $query .= (int)$value.','
     }
     
    $query=substr($query0, -1).')'

  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    Try the following:

    PHP Code:
    if (isset($_POST['checkbox'])) {
     
    $query .= 'WHERE contact_id IN (';
     foreach(
    $_POST['checkbox'] as $value){ 
      
    $query .= (int)$value.','
     }
     
    $query=substr($query0, -1).')'

    Sorry for the late respons, I have tried your code, but no results returned. Also the contact_id in the code above should be event_id.

    What I attempt to do is the following:

    I have a list of events, all with an event_id. When I select an event with the id 21 and press the export button, the query should check if the event_id 21 is present in the contact_events table. If so, it should get the name of that event by a join with the events_manager table (which holds a column with event_id), then it should also get the contact_id linked to that event_id in the contact_events table and get the contact info from the contacts table with the same contact_id.

    Maybe this is a little confusing. Here is the code I have at this moment (with the join query).

    Hope that makes more sense.
    PHP Code:
    $this->pdo $this->connectMySql();
    $query 'SELECT * FROM nbs_contacts, nbs_contact_events JOIN 
             nbs_event_manager ON 
             nbs_contacts.nbs_contact_id = nbs_contact_events.contact_id AND 
             nbs_event_manager.event_id = nbs_contact_events.event_id'
    ;
    if (isset(
    $eventlist)) { 
        
    $query .= ' WHERE event_id IN ('
        foreach(
    $eventlist as $value){  
            
    $query .= (int)$value.',';  
        } 
        
    $query substr($query0, -1).')';  
    }
    $stmt $this->pdo->prepare($query);
    print_r($stmt); // this will be removed later
    if(!$stmt->execute()){
        return 
    false;
    }
    $nart $stmt->rowCount();
    echo 
    "returned count: " $nart// this will be removed later
    if ($nart == 0) {
        return 
    "No records returned!";
    }
    // the code below is used to export the results to a csv file 
    $filename "db_export_selection.csv";

    $handle fopen($filename'w+');
                
    $headers = array();
                
    while (
    $row $stmt->fetch(PDO::FETCH_ASSOC)){
        if(empty(
    $headers)){
            
    $headers array_keys($row);
            
    fputcsv($handle$headers);
        }
        
    fputcsv($handle$row);
    }
    fclose($handle);
    $this->pdo null;
    return 
    true
    This whole code is inside a try catch block.

    If anything is not clear, please ask me so I can explain.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    Which part doesn't work?

    Good how you are adding troubleshooting traps in your code. print_r($stmt); needs an echo around it. Furthermore, I don't think $stmt will tell you anything. Instead, add exit($query); right after you define your query. Then cut and past this directly into MySQL shell (or PHPAdmin, or whatever). Is your query working? This is very important to test before you go any further! Once you get some output, then make sure you are getting the right data. I think you will find you are not since if $eventlist is empty, you will get all your results. Try something like WHERE FALSE OR WHERE event_id IN (1,2,3,4). Never tested it, but maybe WHERE event_id IN () will work so you don't need your FALSE check.

    Once you get it working, then you do one of two things. Add some rows to your file or don't. Try to simplify this code, and have only one return statement.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    Which part doesn't work?

    Good how you are adding troubleshooting traps in your code. print_r($stmt); needs an echo around it. Furthermore, I don't think $stmt will tell you anything. Instead, add exit($query); right after you define your query. Then cut and past this directly into MySQL shell (or PHPAdmin, or whatever). Is your query working? This is very important to test before you go any further!.
    Well the strange thing is that the query 'seems' to executed, because the function doesn't return false (if have message at the front-end that tells me if the function was properly executed.

    If you look at the rowCount part, I should at least get the record count if the query was executed. But I don't get even that echo returned, normally I would have.

    The query seems to be running until after the execute, then suddenly returns true without continuing.

    Any idea?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Ok, I just found something weird, the query still executes even though the table name doesn't exists (I changed it as a test), it doesn't return any value but that's true.

    I don't even get any error message back.

    Does anyone know how that hack that happens?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    the query 'seems' to executed, because the function doesn't return false
    Have you run the query directly with MySQL independently of PHP?
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    Have you run the query directly with MySQL independently of PHP?
    I just change the query to the following as a test:
    PHP Code:
    $query 'SELECT * FROM nbs_contact';  // this should be nbs_contacts 
    And still the query runs without any error.

    If I run the same query in phpmyadmin, I get the message that the table 'nbs_contact' doesn't exist.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,917
    Rep Power
    533
    And still the query runs without any error.

    If I run the same query in phpmyadmin, I get the message that the table 'nbs_contact' doesn't exist.
    You obviously are getting an error when executing the query, you are just not getting an indication of the error.

    Can you do any query? Get his part working first.

    Also, see what echo($stmt->execute()); gives you.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    230
    Rep Power
    8
    Originally Posted by NotionCommotion
    You obviously are getting an error when executing the query, you are just not getting an indication of the error.

    Can you do any query? Get his part working first.

    Also, see what echo($stmt->execute()); gives you.
    I think I figured it out.

    In the try catch (especially the catch part) I had the following code:
    PHP Code:
    catch (PDOException $e
    {
        
    //if($debug_mode) {
            
    $message '<pre>';
            
    $message .= 'Rownumber: '.$e->getLine().'<br>';
            
    $message .= 'File: '.$e->getFile().'<br>';
            
    $message .= 'Error message: '.$e->getMessage().'<br>';
            
    $message .= '</pre>';
            return 
    $message;
        
    //} else {
            //$message = '<p>Ophalen van pagina is mislukt!: ' . $e->getMessage() .'</p>';
        //}
        
    return false;

    Because there were 2 returns, the catch didn't work right.

    So I changed it to the following, but I am not totally sure about it. What I want is that when only me is logged in (the super-admin) the error message shows a specific message and when any other user is logged in, the regular message is shown. This is what I came up with, I suspect it's not the most secure way.
    PHP Code:
    public function exportAll($curuser)
    {
    try 
    {
         
    // code voor de query
    }catch (PDOException $e
    {
        if(
    $curuser === 'super-admin') {
            
    $message '';
            
    $message .= '<pre>';
            
    $message .= 'Regelnummer: '.$e->getLine().'<br>';
            
    $message .= 'Bestand: '.$e->getFile().'<br>';
            
    $message .= 'Foutmelding: '.$e->getMessage().'<br>';
            
    $message .= '</pre>';
            echo 
    $message;
        }
        return 
    false;

    I now get a more detailed message. And the query stops running as it should.

IMN logo majestic logo threadwatch logo seochat tools logo