Help using php to write a mySQL query with if statements

[aaron4osu]

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

 $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"); 

[Cups]

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.

// 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!

[aaron4osu]

Thanks Cups. That got it working. Here is what I have now:

<?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?

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_string, FILTER_VALIDATE_INT);
    }
    return $clean_string;
} 

[Lemon Juice]

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?

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_string, FILTER_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.

[Cups]

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:

  $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:

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

Then that entire script could look as simple as:

$_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.