Insert value from dropdown list in form to MySQL

[dgibbons82]

I am currently working on a simple survey system for educational purposes. I know it is subject to SQL Injection, but I am new to PHP/MySQL and wanted to learn something basic so I have something to build upon later.

Basically, I have an HTML form that processes the PHP on the same page:

<form action="" method="post">
Please enter your first name: <input type="text" name="first_name" size="40" /><br /><br /> 
Please enter your last name: <input type="text" name="last_name" size="40" /><br /><br /> 
A number where you can be reached: <input type="text" name="phone" size="15" /><br /><br />

Was your technical issue resolved? <select name="resolved"><option selected="selected" value="">Select One...</option><option value="yes">Yes</option><option value="no">No</option></select><br /><br /> 

On a scale of 1 - 5, was your technician knowledgeable?<br /> <select name="knowledge"><option selected="selected" value="">Select One...</option><option value="1">1 - The technician was not knowledgeable at all</option><option value="2">2 - The technician was somewhat knowledgeable</option><option value="3">3 - The technician met my standards</option><option value="4">4 - The technician was knowledgeable</option><option value="5">5 - The technician was very knowledgeable</option></select><br /><br /> 

On a scale of 1 - 5, was your technician friendly?<br /><select name="friendly"> <option selected="selected" value="">Select One...</option><option value="1">1 - The technician was not friendly at all</option><option value="2">2 - The technician was somewhat friendly</option><option value="3">3 - The technician met my standards</option><option value="4">4 - The technician was friendly</option><option value="5">5 - The technician was very friendly</option></select><br /><br /> 

On a scale of 1 - 5, was your issue quickly resolved?<br /> <select name="quickness"> <option selected="selected" value="">Select One...</option><option value="1">1 - The issue was not resolved quickly at all</option><option value="2">2 - The issue was resolved somewhat quickly</option><option value="3">3 - The issue resolution time met my standards</option><option value="4">4 - The issue was resolved quickly</option><option value="5">5 - The issue was resolved very quickly</option></select><br /><br /> 

Would you recommend our company to a friend or relative? <select name="referral"><option selected="selected" value="">Select One...</option><option value="yes">Yes</option><option value="no">No</option></select><br /><br />

Do you have any comments, compliments, suggestions, or complaints about your service today?<br /> <textarea name="comments" rows="15" cols="30"></textarea><br /> 

<input type="submit" value="Send Survey" />
</form>

And I would like to capture the form data and record it into my MySQL database. As of now, the text fields insert without any problems. However, the selected dropdown value does not insert. It doesn't insert anything (a blank record) where the dropdown items are (as well as my comments textbox).

My PHP is as follows:

<?php
error_reporting(0);
$con = mysql_connect("localhost","xxxxxxxxx","xxxxxxxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db ("survey", $con);

$sql="INSERT INTO survey (first_name, last_name, phone, resolved, knowledge, friendly, quickness, referral, comments)
VALUES
('$_POST[first_name]','$_POST[last_name]','$_POST[phone]','$POST[resolved]','$POST[knowledge]','$POST[friendly]','$POST[quickness]','$POST[referral]','$POST[comments]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($con)
?>

I have error reporting turned off because I'm using a Joomla template that conflicts with one of my modules.

My database is setup with the following columns:

1. id - Primary - Auto Increment
2. date - Timestamp - CurrentTimestamp
3. first_name - Text
4. last_name - Text
5. phone - Text
6. resolved - Text
7. knowledge - Text
8. friendly - Text
9. quickness - Text
10. referral - Text
11. comments - Text

The id and date fields do what they are supposed to, and the text fields (first name, last name, and phone) all record with no problems. The problem seems to be with the dropdown lists, and the textarea box.

Am I missing something in my PHP in order to capture the values from the dropdown options and comments textarea? I tried removing the selected value of " " from the html but it still records empty records.

Any advice is greatly appreciated! I'm a pretty novice programmer (if I can call myself that for lack of better words), but am learning. Thank you for taking a look.

[siteky]

Take a look at this line:

('$_POST[first_name]','$_POST[last_name]','$_POST[phone]','$POST[resolved]','$POST[knowledge]','$POST[friendly]','$POST[quickness]','$POST[referral]','$POST[comments]')";

You have missing underscores in your $_POST variables.

[memomk]

yup also i would prefare to give a var the values then insert it in the database
e.g:

$firstname=$_POST['firstname'];
$lastname=$_POST['last_name'];
$phone=$_POST['phone'];
$resolved=$_POST['resolved'];
$knowledge=$_POST['knowledge'];
$friendly=$_POST['friendly'];
$quickness=$_POST['quickness'];
$ref=$_POST['referral'];
$comments=$_POST['comments'];
$sql="INSERT INTO survey (first_name, last_name, phone, resolved, knowledge, friendly, quickness, referral, comments)
VALUES
($firstname,$lastname,$phone,$resolved,$knowledge,$friendly,$quickness,$ref,$comments)";

[broj1]

And before inserting them, check the values and/or escape them.

$firstname = trim(mysql_real_escape_string($_POST['firstname']));
...

[dgibbons82]

Thank you everyone for the tips and suggestions. I'll +1 each one when I'm back at my computer. BTW, I'm still learning php and MySQL but what is the reason behind giving a var to each value? Is there a good resource online that explains this?

Thanks again. I really appreciate the help.

[broj1]

Storing posted values in variables is not neccessary by itself but it helps clarity of your code. Compare the following two statements:

// $_POST array elements used 
$sql="INSERT INTO survey (first_name, last_name, ...)
VALUES
('{$_POST[first_name]}','{$_POST[last_name]}', ...);

// variables used
$sql="INSERT INTO survey (first_name, last_name, ...)
VALUES
('$first_name','$last_name', ...);    

The second version is far more readable and easier to debug (please note that you should wrap array elements in curly braces to get them parsed within double quotes since they are composite variables).

Far more important thing is to validate and sanitize the values you get from the user (through $_POST, $_GET, $_COOKIE arrays). The fact is that you can not trust the user input (you will hear this countless times in web development world). The user can input erroneus data and unintentionally break your application. Or even worse the user can intentionally input malicious data in your web form and do damage to your database and you / your client. A simple example is when user enters specially crafted SQL code in your name field and if you do not check it first you will include that SQL code in your query and send it to your database. i.e the malicious code can expose all your users and passwords or allow login without a password. So it is a really good idea to check whether values are what they are supposed to be and sanitize them (by casting integers, escaping strings, using php filters etc.)

So it is a good habbit to use variables and store cleaned values into variables. As an example let's look at the first_name field. What you expect users to enter is a string containing letters, maybe up to two spaces (some names might have spaces in them like Anna Marie) and maybe a dash (Jean-Paul). Everything elese should not be allowed (I guess). So you can use functions like trim() to remove spaces from the beginning and end of the input (users usually do not see them in the input fields if they press spacebar unintentionally), substr_count() to count spaces and dashes,mysql_real_escape_string to escape dangerous characters if they find way into the query, preg_match() for checking using regular expressions etc. If you expect user to enter a number you can use is_numeric() function for checking or you can cast input values to expected type.

See http://php.net/manual/en/security.php.

[memomk]

yup and his code have alot of syntax errors

[broj1]

@memomk
In php code I found only one repeating error which is in the $sql string construction - namely $_POST array elements should be enclosed in curly braces:

$sql="INSERT INTO survey (first_name, last_name, phone, resolved, knowledge, friendly, quickness, referral, comments)
VALUES
('{$_POST[first_name]}','{$_POST[last_name]}','{$_POST[phone]}','{$POST[resolved]}','{$POST[knowledge]}','{$POST[friendly]}','{$POST[quickness]}','{$POST[referral]}','{$POST[comments]}')";

which is what I have already noted in my post above. What other syntax errors are there?

[broj1]

I have only now read your original post thoroughly, sorry.

When the form is submitted you have to first check whether all values are present at all. If there is a value missing your query will break if you do not shoot an error message or provide a default value:

// check the existence of each field
if(isset($_POST[first_name]) && !empty($_POST[first_name])) {
    $first_name = trim(mysql_real_escape_string($_POST[first_name]));
} else {
    // prepare an erro message
    error_msg[] = 'You must provide your first name!';
    // or provide a default value
    $first_name = 'Nameless';
}
// do this for every input field of the form
...

[broj1]

You can also echo your query before submitting it. Put this on line 14 of the php file:

die($sql);

Now you can examine your query and test it in phpmyadmin.

[dgibbons82]

Thank you everyone for the great responses.

Does anyone know of a good link for learning about filtering MySQL queries? I don't expect anyone to explain it on here, but a link to a good resource would be very handy. Or if anyone knows of a good book that explains some of the basic elements of PHP and MySQL. I'm open to any suggestions.

[broj1]

http://www.w3schools.com/ is a great resource for a beginner. For filtering input values see http://www.w3schools.com/php/php_filter.asp.