SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help using php to write a mySQL query with if statements

    I'm trying to process a 8 question survey where each question is not mandatory. So I never know which fields are filled out until i'm processing them. The questions are radio buttons with either a "yes" or "no" value.

    I was thinking about creating a query string for both the fields and values and then insert those values into a insert string. I can't seem to figure out how to know things like which will be the last fields with a value so I don't put in another comma.

    Any ideas on how best to process this?

    Here is what I have so far
    PHP Code:
     $fields "";
      
    $values "";

      if(!empty(
    $_POST['q1']))    
        {
          
    $fields .= "a1";
          
    $values .= " '".$_POST['q1']."' ";
        }

      if(!empty(
    $_POST['q2']))    
        {
          
    $fields .= "a2";
          
    $values .= " '".$_POST['q2']."' ";
        }

      ...
      ...
      ...

      
    $survey_query "INSERT INTO survey (".$fields.")VALUES(".$values.")";

    // this is what the insert statement should look like if all fields were filled out
    // INSERT INTO survey (voter_id, a1, a2, a3, a4, a5, a6, a7, a8)
    //VALUES ("255.255.255.255", "yes", "yes", "no", "yes", "yes", "yes", "no", "yes"); 

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Good question. and there are a few ways to do what you want.

    On the face of it and to address your immediate question, use rtrim() to rm the trailing comma. (PHP permits a trailing comma in array definitions, say, but SQL is far more of a stickler).

    Say your computed $fields string was:

    "q1,q2,"

    and you want:

    "q1,q2,"

    Then use

    rtrim(",", $fields);

    With values you could do similar.

    PHP Code:

    // spoofing a form submission ...

    $_POST['q1'] = "yes";
    $_POST['q2'] = "no";

     
    $fields "";
      
    $values "";

      if ( isset(
    $_POST['q1']) )    
        {
          
    $fields .= "a1,";  // add the commas here as strings
          
    $values .= "'".$_POST['q1']."',"// ditto
        
    }

    echo 
    $fields ;

      if(isset(
    $_POST['q2']))
        {
          
    $fields .= "a2,";
          
    $values .= "'".$_POST['q2']."',";
        }

    $survey_query "INSERT INTO survey (" rtrim($fields',') . ") VALUES (" .rtrim($values','). ")";

    echo 
    $survey_query
    Get this working and then we can explore other ways of achieving this without all those if statements.

    hint: you are taking an array ($_POST) and could be semi-automating the handling of this array transforming it into the array you want without the messy rtrim, AND making sure the values are properly escaped for your database, AND checking the the keys being submitted are those you are expecting, but achieving all this in far less code. Sounds good?

    DO NOT use this on a live site yet, you are wide open to SQL injection attacks!

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2011
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Cups. That got it working. Here is what I have now:
    PHP Code:
    <?php
    session_start
    ();
    require_once 
    'app_config.php';
    require_once 
    'database_connection.php';
    require_once 
    'clean_up.php';

    $ip=$_SERVER['REMOTE_ADDR'];
    //echo 'IP Address: '.$ip.'<br><br>';

    $query 'Select * from voted where ip = "'.$ip.'";';
    $result mysql_query($query);

    $num_rows mysql_num_rows($result);

       
    if(
    $num_rows 1){ // if the user has not already voted

      
    $q1 mr_clean($_POST['q1']);
      
    $q2 mr_clean($_POST['q2']);
      
    $q3 mr_clean($_POST['q3']);
      
    $q4 mr_clean($_POST['q4']);
      
    $q5 mr_clean($_POST['q5']);
      
    $q6 mr_clean($_POST['q6']);
      
    $q7 mr_clean($_POST['q7']);
      
    $q8 mr_clean($_POST['q8']);


      
    $fields "voter_id, ";
      
    $values "'".$ip."', ";

      if (!empty(
    $q1))    
        {
          
    $fields .= "a1,";  // add the commas here as strings
          
    $values .= "'".$q1."',"// ditto
      
    }

      if(!empty(
    $q2))
        {
          
    $fields .= "a2,";
          
    $values .= "'".$q2."',";
      }

      if(!empty(
    $q3))
        {
          
    $fields .= "a3,";
          
    $values .= "'".$q3."',";
      }

      if(!empty(
    $q4))
        {
          
    $fields .= "a4,";
          
    $values .= "'".$q4."',";
      }

      if(!empty(
    $q5))
        {
          
    $fields .= "a5,";
          
    $values .= "'".$q5."',";
      }
      if(!empty(
    $q6))
        {
          
    $fields .= "a6,";
          
    $values .= "'".$q6."',";
      }

      if(!empty(
    $q7))
        {
          
    $fields .= "a7,";
          
    $values .= "'".$q7."',";
      }

      if(!empty(
    $q8))
        {
          
    $fields .= "a8,";
          
    $values .= "'".$q8."',";
      }


      
    //echo "fields: ".$fields;
      //echo " values: ".$values;

      
    $survey_query "INSERT INTO survey (" rtrim($fields',') . ") VALUES (" .rtrim($values','). ");";

      
    //echo "query: ".$survey_query;
      
    $result mysql_query($survey_query);

    //end if num < 1 

    ?>
    As far as the SQL injection attacks here is a function I created a while back. I just hadn't implemented it yet for simplicity reasons. Does this still cover most types of security issues?
    PHP Code:
    function mr_clean($dirty_string$type NULL){

        
    $clean_string trim($dirty_string);
        
    // $clean_string = mysql_real_escape_string($dirty_string);

        
    $clean_string strip_tags($clean_string);
        
    $clean_string htmlspecialchars($clean_string);

        if (
    $type == 'request'){
            return 
    filter_var($clean_stringFILTER_VALIDATE_INT);
        }
        return 
    $clean_string;


  4. #4
    SitePoint Guru bronze trophy
    Join Date
    Dec 2003
    Location
    Poland
    Posts
    930
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by aaron4osu View Post
    As far as the SQL injection attacks here is a function I created a while back. I just hadn't implemented it yet for simplicity reasons. Does this still cover most types of security issues?
    PHP Code:
    function mr_clean($dirty_string$type NULL){

        
    $clean_string trim($dirty_string);
        
    // $clean_string = mysql_real_escape_string($dirty_string);

        
    $clean_string strip_tags($clean_string);
        
    $clean_string htmlspecialchars($clean_string);

        if (
    $type == 'request'){
            return 
    filter_var($clean_stringFILTER_VALIDATE_INT);
        }
        return 
    $clean_string;

    Actually, as far as SQL injections are concerned out of the above functions only mysql_real_escape_string() is necessary and is the important one (why is it commented out then?) - you can use the others depending on what you want to do with the data but they are not necessary for security when inserting into the db. Just mysql(i)_real_escape_string() for strings and for numbers - cast to a number - (float) or (int) - or pass them through a filter with FILTER_SANITIZE_NUMBER_INT or FILTER_SANITIZE_NUMBER_FLOAT.

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by aaron4osu View Post
    As far as the SQL injection attacks here is a function I created a while back. I just hadn't implemented it yet for simplicity reasons.
    Fair enough mate, I will presume from now on that the security issue is dealt with, its just when we see sql builders which do not contain protection or that do not state "protection removed for the sake of brevity" its incumbent on us to start getting preachy ...

    (though you should follow the advice of @Lemon Juice

    Now consider something.

    Lets say your form question elements contained the same name attribute as your table. At the moment you have "q1" which you store in "a1".

    Imagine you altered your database table to have fields named like "q1" as well.

    This means you would have repeating code like this:

    PHP Code:
      $q1 mr_clean($_POST['q1']);
      
    $q2 mr_clean($_POST['q2']);

    //  $fields = "voter_id, ";
    //  $values = "'".$ip."', ";

      
    if (!empty($q1))    
        {
          
    $fields .= "q1,";  // add the commas here as strings
          
    $values .= "'".$q1."',"// ditto
      
    }

      if(!empty(
    $q2))
        {
          
    $fields .= "q2,";
          
    $values .= "'".$q2."',";
      } 
    Do you see how much typing you are doing of the exact same variable name? q1 is being typed 5 times, ditto q2 etc.

    Imagine this was your incoming post variables:

    PHP Code:
    $_POST['q1'] = "yes";
    $_POST['q2'] = "no"
    Then that entire script could look as simple as:

    PHP Code:
    $_POST['q1'] = "yes";
    $_POST['q2'] = "no";

    $fields "voter_id, ";
    $values "'".$ip."', ";

    foreach(
    $_POST as $key=>$value ){
    $fields .= $key ',';
    $values .= "'$value',";
    }

    $survey_query "INSERT INTO survey (" rtrim($fields',') . ") VALUES (" .rtrim($values','). ")"
    There are some caveats, some ways to improve this and more security to add to it, but do you see that your code can be handled as arrays too?

    Have a play with that, var_dump out some of the vars as you see fit - and let me know what you think if you want to continue to improve this.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •