Page 1 of 2

Send to MYSQL Database -SOLVED

Posted: Fri Apr 02, 2010 12:53 am
by mfandel
I am trying to build a form that will post data to the MYSQL database.
I don't get any errors, but the data is not appearing in my database.
Any help is appreciated. I used sample code from another site to build this, but made a mistake somewhere.
I changed the database logins for security.
front end can be viewed at:
http://www.avsabonline.org/admin_form.php

Code: Select all

<?php
$db_host = "localhost";
$db_user = "avsab_user";
$db_pwd = "pwd";
$db_name = "avsab_registration";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>
<?php include 'states.php' ?>
<br />
<?php
function showOptionsDrop($array){
$string = '';
foreach($array as $k => $v){        
$string .= '<option value="'.$k.'"'.$s.'>'.$v.'</option>'."\n";     
}
return $string;
}
$em = $_GET[email];
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<link rel="stylesheet" href="http://avsabonline.org/avsabonline/administrator/templates/minted_one-point-five/css/template_css.css" type="text/css" />
<link rel="stylesheet" href="http://avsabonline.org/avsabonline/administrator/templates/minted_one-point-five/css/theme.css" type="text/css" />
<title>AVSAB Scientific Session Manual Add</title>
<style>
body{font-family:arial;color:#333333;font-size:12px;}
.space{padding-right:20px;padding-bottom:20px;}
select{font-family:arial;color:#333333;width:100px;font-size:12px;}
input{font-size:12px;border:1px solid #cccccc;background:#eeeeee;}
a:link{color:#1E90FF;text-decoration:none;}
a:visited{color:#1E90FF;text-decoration:none;}
a:active{color:#1E90FF;text-decoration:none;}
a:hover{color:#1E90FF;text-decoration:none;}
</style>
<link rel="stylesheet" type="text/css" href="reset.css">
<link rel="stylesheet" type="text/css" href="form_style.css">
<link rel="stylesheet" type="text/css" href="form_style2.css">
<script>
function autotab(original,destination){
if (original.getAttribute&&original.value.length==original.getAttribute("maxlength"))
destination.focus()
}
</script>
</head>
<body>
<center>
<div style="width:820px;">
<table style="width:720px;"">
<tr>
<td>
<?php
if (!isset($_POST['submit'])) {
?>
<div id="container">
<form method="post" name="avsab">
<fieldset id="personal" class="repeat">
<legend>Personal Information</legend>

<div style="float:left;">
<span class="oneField">
<label class="preField">Prefix
<select name="prefix"><?php echo showOptionsDrop($prefix_arr); ?></select>
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Degree / Suffix for badge
<select name="degree"><?php echo showOptionsDrop($degree_arr); ?></select>
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Other
<input TYPE="text" NAME="otherdegree" size="20"></label>
</span>
</div>
<br><br><br><br>
<div style="float:left;">
<span class="oneField">
<label class="preField">Last (family) Name
<input TYPE="text" NAME="lastname" size="36">
<span class="reqMark">*</span></label><br></span>
</div>
<div style="float:left;">
<span class="oneField">
<label class="preField">First (given) Name
<input TYPE="text" NAME="firstname" size="36">
<span class="reqMark">*</span></label><br></span>
</div>
<br><br><br><br>
<div style="float:left;">
<span class="oneField">
<label class="preField">If Student List College(s) Attended
<input TYPE="text" NAME="college" size="40">
<span class="reqMark">&nbsp;</span></label><br></span>
</div>
<div>
<span class="oneField">
<label class="preField">Year of Graduation
<input TYPE="text" style="text-align:left;" id="gradyear" NAME="gradyear" size="15"  value=""></label>
</span>
</div>
<br /><br />
<div style="float:left;">
<style>.radio_tag{font-size:12px;}.radio_tag input{border:none;}.other_tag{font-size:12px;}</style>
<label class="preField" style="float:left;v">Dietary Preference</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="No Preference">No Preference</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Vegetarian">Vegetarian</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Vegan">Vegan</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Other">Other</label>
<label class="other_tag"><input TYPE="text"  name="other_dietary_preference" size="20"></label>
</div>
</fieldset>
<fieldset id="contact" class="">
<legend>Contact Information</legend>


<span class="oneField">
<label class="preField">Address
<input TYPE="text" NAME="address" size="120">
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>


<div style="float:left;">
<span class="oneField">
<label class="preField">City
<input TYPE="text" NAME="city" size="50">
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">State/Province
<select name="state"><?php echo showOptionsDrop($states_arr); ?></select></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Poastal Code
<input TYPE="text" NAME="zipcode" size="10">
<span class="reqMark">*</span></label><br></span>
</div>
<br /><br /><br /><br />
<div style="float:left;">
<span class="oneField">
<label class="preField">Country
<select name="country"><?php echo showOptionsDrop($country_arr); ?></select>
<span class="reqMark"></span></label><br></span>
</div>
<div style="float:left;">
<span class="oneField">
<label class="preField">Email
<input TYPE="text" NAME="email" size="50" value="<?php echo $em ?>">
<span class="reqMark">*</span></label><br></span>
</div>
<br /><br /><br /><br />
<div id="phone" style="float:left;">
<span class="oneField">
<label class="preField">Daytime Phone 
<input type="text" name="phone_first" size=4 onKeyup="autotab(this, document.avsab.phone_second)" maxlength=3> <input type="text" name="phone_second" size=4 onKeyup="autotab(this, document.avsab.phone_third)" maxlength=3> <input type="text" name="phone_third" size=5 maxlength=4>
<span class="reqMark"></span></label><br></span>
</div>
<div id="phone" style="float:left;">
<span class="oneField">
<label class="preField">Fax
<input type="text" name="fax_first" size=4 onKeyup="autotab(this, document.avsab.fax_second)" maxlength=3> <input type="text" name="fax_second" size=4 onKeyup="autotab(this, document.avsab.fax_third)" maxlength=3> <input type="text" name="fax_third" size=5 maxlength=4>
<span class="reqMark"></span></label><br></span>
</div>
<br /><br /><br /><br />
<div style="padding-top:40px;">
<div style="float:right;">
<input type="hidden" name="year" value="2010">
<input type="submit" name="submit" value="Submit Registration">
</div>
</div>
</fieldset>
</div><!--eof container-->
</body>
</html>
<?php
}else{
?>
<?php
$phone_main = '('.$_POST['phone_first'].') '.$_POST['phone_second'].'-'.$_POST['phone_third'];
$fax_main = '('.$_POST['fax_first'].') '.$_POST['fax_second'].'-'.$_POST['fax_third'];
$prefix = $_POST['prefix'];
$degree = $_POST['degree'];
$otherdegree = $_POST['otherdegree'];
$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
$country = $_POST['country'];
$email = $_POST['email'];
$phone = $phone_main;
$fax = $fax_main;
$college = $_POST['college'];
$gradyear = $_POST['gradyear'];
$year = $_POST['year'];
$dietary_preference = $_POST['dietary_preference'];
$other_dietary_preference = $_POST['other_dietary_preference'];
mysql_query("INSERT INTO `register` (prefix,degree,otherdegree,lastname,firstname,address,city,state,zipcode,country,email,phone,fax,college,gradyear,year,dietary_preference,other_dietary_preference) VALUES ('$prefix','$degree','$otherdegree','$lastname','$firstname','$address','$city','$state','$zipcode','$country','$email','$phone','$fax','$college','$gradyear','$year','$dietary_preference','$other_dietary_preference')");
echo "";
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>ACVB/AVSAB Behavior Symposium - July 30, 2010</title>
<link rel="stylesheet" type="text/css" href="reset.css">
<link rel="stylesheet" type="text/css" href="form_style.css">
<link rel="stylesheet" type="text/css" href="form_style2.css">
<script>
function autotab(original,destination){
if (original.getAttribute&&original.value.length==original.getAttribute("maxlength"))
destination.focus()
}
</script>
</head>
<body>
<div>
Thank-You. The registrant has been submitted. <a href="http://avsabonline.org/avsabonline/administrator/addon3.php">Return to Annual Meeting Registrants.</a>
</div>

<?
}
?>
</table>
</div>
</center>
</body>
</html>

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 2:11 am
by dejvos
Have you tried to switch error reporting to the lowest level?

Code: Select all

<?php
error_reporting(E_ALL);
?

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 2:16 am
by mfandel
I have not, is there a certain place I should put that.
I am not very familiar with PHP at all.
No clue what that would do either.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 2:21 am
by mfandel
ok, I think I figured out how to put that in there.
It actually gave me a note that I missed a field on my test, so I assume it worked.

When I went back and corrected my error in filling out the form, it again appeared to process fine, but nothing in the database.

Could it be something with a server setting?
The only reason I ask is because this form was used last year without issue and now all of a sudden it doesn't work.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 2:53 am
by dejvos
May be the problem is in the states.php which is included. Try to check if you don't rewrite some global variable there or you can try to use link to DB resource in each mysql_*() function. I can't realize anything else right now.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 3:14 am
by mfandel
Thansk for the responses!
the state.php is jsut for the drop downs... I included the code just to be safe.
As for you what you meant by link to db resource in each MYSQL_*() function
I am not familiar with what that means can you help explain a little more or point me in the right direction.
I am not very familiar with php/mysql

THanks

Code: Select all

<?php 
$degree_arr = array(' '=>" ",'DVM'=>"DVM", 'VMD'=>"VMD", 'RVT'=>"RVT", 'LVT'=>"LVT");
$prefix_arr = array(' '=>" ",'Dr'=>"Dr", 'Mr'=>"Mr", 'Ms'=>"Ms");

  
$states_arr = array('AL'=>"Alabama",'AK'=>"Alaska",'AZ'=>"Arizona",'AR'=>"Arkansas",'CA'=>"California",'CO'=>"Colorado",'CT'=>"Connecticut",'DE'=>"Delaware",'DC'=>"DC",'FL'=>"Florida",'GA'=>"Georgia",'HI'=>"Hawaii",'ID'=>"Idaho",'IL'=>"Illinois", 'IN'=>"Indiana", 'IA'=>"Iowa",  'KS'=>"Kansas",'KY'=>"Kentucky",'LA'=>"Louisiana",'ME'=>"Maine",'MD'=>"Maryland", 'MA'=>"Massachusetts",'MI'=>"Michigan",'MN'=>"Minnesota",'MS'=>"Mississippi",'MO'=>"Missouri",'MT'=>"Montana",'NE'=>"Nebraska",'NV'=>"Nevada",'NH'=>"New Hampshire",'NJ'=>"New Jersey",'NM'=>"New Mexico",'NY'=>"New York",'NC'=>"North Carolina",'ND'=>"North Dakota",'OH'=>"Ohio",'OK'=>"Oklahoma", 'OR'=>"Oregon",'PA'=>"Pennsylvania",'RI'=>"Rhode Island",'SC'=>"South Carolina",'SD'=>"South Dakota",'TN'=>"Tennessee",'TX'=>"Texas",'UT'=>"Utah",'VT'=>"Vermont",'VA'=>"Virginia",'WA'=>"Washington",'WV'=>"West Virginia",'WI'=>"Wisconsin",'WY'=>"Wyoming", 
    'AB' => "Alberta",
    'BC' => 'British Columbia',
    'MB' => 'Manitoba',
    'NB' => 'New Brunswick',
    'NL' => 'Newfoundland',
    'NT' => 'NW Territories',
    'NS' => 'Nova Scotia',
    'NU' => 'Nunavut',
    'ON' => 'Ontario',
    'PE' => 'P.E. Island',
    'QC' => 'Quebec',
    'SK' => 'Saskatchewan',
    'YT' => 'Yukon Territory');

$country_arr = array(
'US'=>'United States',
'AF'=>'Afghanistan',
'AL'=>'Albania',
'DZ'=>'Algeria',
'AS'=>'American Samoa',
'AD'=>'Andorra',
'AO'=>'Angola',
'AI'=>'Anguilla',
'AQ'=>'Antarctica',
'AG'=>'Antigua And Barbuda',
'AR'=>'Argentina',
'AM'=>'Armenia',
'AW'=>'Aruba',
'AU'=>'Australia',
'AT'=>'Austria',
'AZ'=>'Azerbaijan',
'BS'=>'Bahamas',
'BH'=>'Bahrain',
'BD'=>'Bangladesh',
'BB'=>'Barbados',
'BY'=>'Belarus',
'BE'=>'Belgium',
'BZ'=>'Belize',
'BJ'=>'Benin',
'BM'=>'Bermuda',
'BT'=>'Bhutan',
'BO'=>'Bolivia',
'BA'=>'Bosnia',
'BW'=>'Botswana',
'BV'=>'Bouvet Island',
'BR'=>'Brazil',
'IO'=>'British IO',
'BN'=>'Brunei',
'BG'=>'Bulgaria',
'BF'=>'Burkina Faso',
'BI'=>'Burundi',
'KH'=>'Cambodia',
'CM'=>'Cameroon',
'CA'=>'Canada',
'CV'=>'Cape Verde',
'KY'=>'Cayman Islands',
'CF'=>'C.African Republic',
'TD'=>'Chad',
'CL'=>'Chile',
'CN'=>'China',
'CX'=>'Christmas Island',
'CC'=>'Cocos Islands',
'CO'=>'Columbia',
'KM'=>'Comoros',
'CG'=>'Congo',
'CK'=>'Cook Islands',
'CR'=>'Costa Rica',
'CI'=>'Cote D\'Ivorie',
'HR'=>'Croatia (Hrvatska)',
'CU'=>'Cuba',
'CY'=>'Cyprus',
'CZ'=>'Czech Republic',
'CD'=>'Congo (Zaire)',
'DK'=>'Denmark',
'DJ'=>'Djibouti',
'DM'=>'Dominica',
'DO'=>'Dominican Republic',
'TP'=>'East Timor',
'EC'=>'Ecuador',
'EG'=>'Egypt',
'SV'=>'El Salvador',
'GQ'=>'Equatorial Guinea',
'ER'=>'Eritrea',
'EE'=>'Estonia',
'ET'=>'Ethiopia',
'FK'=>'Falkland Islands',
'FO'=>'Faroe Islands',
'FJ'=>'Fiji',
'FI'=>'Finland',
'FR'=>'France',
'FX'=>'France, Metropolitan',
'GF'=>'French Guinea',
'PF'=>'French Polynesia',
'TF'=>'French Territories',
'GA'=>'Gabon',
'GM'=>'Gambia',
'GE'=>'Georgia',
'DE'=>'Germany',
'GH'=>'Ghana',
'GI'=>'Gibraltar',
'GR'=>'Greece',
'GL'=>'Greenland',
'GD'=>'Grenada',
'GP'=>'Guadeloupe',
'GU'=>'Guam',
'GT'=>'Guatemala',
'GN'=>'Guinea',
'GW'=>'Guinea-Bissau',
'GY'=>'Guyana',
'HT'=>'Haiti',
'HM'=>'Heard Islands',
'HN'=>'Honduras',
'HK'=>'Hong Kong',
'HU'=>'Hungary',
'IS'=>'Iceland',
'IN'=>'India',
'ID'=>'Indonesia',
'IR'=>'Iran',
'IQ'=>'Iraq',
'IE'=>'Ireland',
'IL'=>'Israel',
'IT'=>'Italy',
'JM'=>'Jamaica',
'JP'=>'Japan',
'JO'=>'Jordan',
'KZ'=>'Kazakhstan',
'KE'=>'Kenya',
'KI'=>'Kiribati',
'KW'=>'Kuwait',
'KG'=>'Kyrgyzstan',
'LA'=>'Laos',
'LV'=>'Latvia',
'LB'=>'Lebanon',
'LS'=>'Lesotho',
'LR'=>'Liberia',
'LY'=>'Libya',
'LI'=>'Liechtenstein',
'LT'=>'Lithuania',
'LU'=>'Luxembourg',
'MO'=>'Macau',
'MK'=>'Macedonia',
'MG'=>'Madagascar',
'MW'=>'Malawi',
'MY'=>'Malaysia',
'MV'=>'Maldives',
'ML'=>'Mali',
'MT'=>'Malta',
'MH'=>'Marshall Islands',
'MQ'=>'Martinique',
'MR'=>'Mauritania',
'MU'=>'Mauritius',
'YT'=>'Mayotte',
'MX'=>'Mexico',
'FM'=>'Micronesia',
'MD'=>'Moldova',
'MC'=>'Monaco',
'MN'=>'Mongolia',
'MS'=>'Montserrat',
'MA'=>'Morocco',
'MZ'=>'Mozambique',
'MM'=>'Myanmar (Burma)',
'NA'=>'Namibia',
'NR'=>'Nauru',
'NP'=>'Nepal',
'NL'=>'Netherlands',
'AN'=>'Netherlands Antilles',
'NC'=>'New Caledonia',
'NZ'=>'New Zealand',
'NI'=>'Nicaragua',
'NE'=>'Niger',
'NG'=>'Nigeria',
'NU'=>'Niue',
'NF'=>'Norfolk Island',
'KP'=>'North Korea',
'MP'=>'Mariana Islands',
'NO'=>'Norway',
'OM'=>'Oman',
'PK'=>'Pakistan',
'PW'=>'Palau',
'PA'=>'Panama',
'PG'=>'Papua New Guinea',
'PY'=>'Paraguay',
'PE'=>'Peru',
'PH'=>'Philippines',
'PN'=>'Pitcairn',
'PL'=>'Poland',
'PT'=>'Portugal',
'PR'=>'Puerto Rico',
'QA'=>'Qatar',
'RE'=>'Reunion',
'RO'=>'Romania',
'RU'=>'Russia',
'RW'=>'Rwanda',
'SH'=>'Saint Helena',
'KN'=>'Saint Kitts',
'LC'=>'Saint Lucia',
'PM'=>'Saint Pierre',
'VC'=>'Saint Vincent',
'SM'=>'San Marino',
'ST'=>'Sao Tome And Principe',
'SA'=>'Saudi Arabia',
'SN'=>'Senegal',
'SC'=>'Seychelles',
'SL'=>'Sierra Leone',
'SG'=>'Singapore',
'SK'=>'Slovak Republic',
'SI'=>'Slovenia',
'SB'=>'Solomon Islands',
'SO'=>'Somalia',
'ZA'=>'South Africa',
'GS'=>'South Georgia',
'KR'=>'South Korea',
'ES'=>'Spain',
'LK'=>'Sri Lanka',
'SD'=>'Sudan',
'SR'=>'Suriname',
'SJ'=>'Svalbard ',
'SZ'=>'Swaziland',
'SE'=>'Sweden',
'CH'=>'Switzerland',
'SY'=>'Syria',
'TW'=>'Taiwan',
'TJ'=>'Tajikistan',
'TZ'=>'Tanzania',
'TH'=>'Thailand',
'TG'=>'Togo',
'TK'=>'Tokelau',
'TO'=>'Tonga',
'TT'=>'Trinidad And Tobago',
'TN'=>'Tunisia',
'TR'=>'Turkey',
'TM'=>'Turkmenistan',
'TC'=>'Turks And Caicos',
'TV'=>'Tuvalu',
'UG'=>'Uganda',
'UA'=>'Ukraine',
'AE'=>'United Arab Emirates',
'UK'=>'United Kingdom',
'US'=>'United States',
'UM'=>'US Outlying Islands',
'UY'=>'Uruguay',
'UZ'=>'Uzbekistan',
'VU'=>'Vanuatu',
'VA'=>'Vatican City (Holy See)',
'VE'=>'Venezuela',
'VN'=>'Vietnam',
'VG'=>'Virgin Islands (GB)',
'VI'=>'Virgin Islands (US)',
'WF'=>'Wallis And Futuna',
'EH'=>'Western Sahara',
'WS'=>'Western Samoa',
'YE'=>'Yemen',
'YU'=>'Yugoslavia',
'ZM'=>'Zambia',
'ZW'=>'Zimbabwe'
);

?>

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 4:07 am
by dejvos
Links are used like bellow:

Code: Select all

<?php
$link = mysql_connect('localhost','user','very secret password'); // $link - contains mysql resource; it's like a link to a DB
if(!$link) die('ERROR');
$db_connect = mysql_select_db('db',$link);
if(!$db_connect) die('ERROR');
//...
mysql_query($query,$link);

?>
I'm not sure about this stuff. I don't know if using of resources could be forced by php.ini, you would try it and you would see.

Good luck!

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 4:21 am
by mfandel
ok, I assume the step was to replace the way I was connecting to the database with what you provided.
I did that and tested the form.

Same result, nothing going to database.
No errors either.

I have been searching all over.
Is it possible that it has something to do with the "post" and security on the server, register_globals?
I just read some stuff on that and was wondering.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 5:55 pm
by minorDemocritus
mfandel,

A few comments first. Near the end of your script, the way you construct the SQL query is SCARY. You're putting raw, unvalidated and unescaped data from the user directly into the SQL query... that is a BAD THING.

This is BAD:

Code: Select all

$prefix = $_POST['prefix'];
This is STILL BAD, but a lot better:

Code: Select all

$prefix = mysql_real_escape_string($_POST['prefix']);
Not only is the data not validated, so innocent mistakes can cause a mess in the database, but you're leaving your database completely open to SQL injection! All an attacker has to do is enter something like this into one of the form elements....

Code: Select all

garbage; DROP ALL TABLES;
...and then ALL YOUR TABLES ARE GONE.

The rule to follow is NEVER TRUST USER INPUT. Code a routine to validate user input, and at the VERY LEAST, use mysql_real_escape_string() to avoid SQL injection.

I will follow up with advice on figuring out the problem you originally posted.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 6:24 pm
by minorDemocritus
With that warning out the the way, let's get to trying to isolate the problem.

Here are a couple debugging tricks that I've found to be very helpful.
1. Simple, but very helpful stuff:

Code: Select all

echo 'Line number ' . __LINE__ . ' was executed.<br />';
Put this snippet inside loops and if/elseif/else statements. It will tell you if a particular line in the script was been executed. This will give you information about the path that the script is taking. It will show up like:

Code: Select all

Line number 54 was executed.
Line number 182 was executed.
2. Echoing variables helps, but var_dump() is even better.
Instead of putting the variables directly in the SQL query, use separate variables for the query string.

This...

Code: Select all

mysql_query("INSERT INTO `register` (prefix,degree,otherdegree,lastname,firstname,address,city,state,zipcode,country,email,phone,fax,college,gradyear,year,dietary_preference,other_dietary_preference) VALUES ('$prefix','$degree','$otherdegree','$lastname','$firstname','$address','$city','$state','$zipcode','$country','$email','$phone','$fax','$college','$gradyear','$year','$dietary_preference','$other_dietary_preference')");
Becomes this...

Code: Select all

// Put the SQL query string together
$query = "INSERT INTO `register`
(prefix, degree, otherdegree, lastname, firstname,
address, city, state, zipcode, country,
email, phone, fax, college, gradyear,
year, dietary_preference, other_dietary_preference)
VALUES
('$prefix', '$degree', '$otherdegree', '$lastname', '$firstname',
'$address', '$city', '$state', '$zipcode', '$country',
'$email', '$phone', '$fax', '$college', '$gradyear',
'$year', '$dietary_preference', '$other_dietary_preference')";

// Perform the query
$queryResult = mysql_query($query);

// Dump the result for debugging purposes
echo '<br />The SQL query that was attempted was:<br />';
var_dump($query);
echo '<br />The queryResult resource was:<br />';
var_dump($queryResult);
There are a few things you'll want to notice about this... First, I've broken up the SQL query to multiple lines. This makes it easier to read, and therefore, easier to understand. Second, by splitting the query string from the query execution, we can dump out the query itself, as well as the resource that the query execution returned.

Edit your script to use that form instead, run it, and find the SQL query that var_dump gives you (it will be under 'The SQL query that was attempted was:'). See what kind of result you get if you just run that SQL query directly on the database (phpMyAdmin is great for this). This will directly give you any SQL errors that may be occuring.

Re: Send to MYSQL Database

Posted: Fri Apr 02, 2010 10:11 pm
by mfandel
Thanks for the input. I am away for a quick trip. Will try when i get back Sunday and see if Ican follow your info on the security issue. I got the point but need to read again to make sure I understand how to address. Thanks for the info again. I tried adding some other debug stuff today and I see that there is something wrong around line 21 withe the email script.

Re: Send to MYSQL Database

Posted: Sat Apr 03, 2010 1:54 am
by minorDemocritus
EDIT: AHA! I believe I found at least the reason for your email troubles:

The error that the script is giving:

Code: Select all

Notice: Use of undefined constant email - assumed 'email' in /home/avsab/public_html/admin_form.php on line 21

Notice: Undefined index: email in /home/avsab/public_html/admin_form.php on line 21
In the script, this:

Code: Select all

$em = $_GET[email];
Should be:

Code: Select all

$em = $_GET['email'];
PHP assumes that anything that is not enclosed in quotes, prefixed with $, ending with (), or a reserved word, is a constant. That's why you're getting that error. It assumes the word 'email' is a constant, can't find anything in the script that defines the constant 'email', and assumes that its value should be 'email'. It works (kindof) when the GET value for 'email' is set in the URL, such as admin_form.php?email=example@example.com, but the PHP parser isn't happy with it, hence the Undefined Constant error.

Also, you should really check to make sure the GET value is set before assigning it to a variable, so use something like this:

Code: Select all

if (isset($_GET['email'])) {
        $em = $_GET['email'];
} else {
        echo 'ERROR: Expected "email" value to be set by GET method, none found<br />';
}
Regarding the security issue:

The easiest way I've found to use mysql_real_escape_string() while constructing SQL querys is to use sprintf().

Example:

Code: Select all

$email = 'example@example.com';
$password = 'secret';
$salt1 = '3n9U';
$salt2 = 'k7E1';
$token = sha1($salt1 . $password . $salt2);
$query = sprintf("SELECT * FROM `user_table`
        WHERE `email` = '%s' AND `passwordhash` = '%s'",
        mysql_real_escape_string($email),
        mysql_real_escape_string($token)
);
echo $query;
sprintf() will replace each instance of %s inside the first parameter with the next parameters. The manual page for sprintf() explains it a lot better that I can in this short post, so check that out. My example should output the following, assuming that you make a database connection first.

Code: Select all

SELECT * FROM `user_table`
        WHERE `email` = 'example@example.com' AND `passwordhash` = '53faa222809c832c8e6ea91a796643d8409a50ff'
I've included general good practice in the example, that is, don't store passwords in plaintext!

Re: Send to MYSQL Database

Posted: Mon Apr 05, 2010 12:26 am
by mfandel
Some amazing help and educational. Learning a lot, but still not getting there.
A couple questions or a section that i was not able to follow:
echo 'Line number ' . __LINE__ . ' was executed.<br />';
place this in loops and if, can you explain how I would place that in to make that work?


Also I made all the other changes suggested.. the dump results for debuging produced this after I tried the form, but still nothgin in the database:
The SQL query that was attempted was:
string(393) "INSERT INTO `register` (prefix, degree, otherdegree, lastname, firstname, address, city, state, zipcode, country, email, phone, fax, college, gradyear, year, dietary_preference, other_dietary_preference) VALUES ('Dr', ' ', '', 'Fandel', 'michael', 'test', 'test', 'MI', '49653', 'US', 'michael@galiantsolutions.com', '(231) 256-7588', '(231) 256-5555', '', '1993', '2009', 'No Preference', '')"
The queryResult resource was:
string(393) "INSERT INTO `register` (prefix, degree, otherdegree, lastname, firstname, address, city, state, zipcode, country, email, phone, fax, college, gradyear, year, dietary_preference, other_dietary_preference) VALUES ('Dr', ' ', '', 'Fandel', 'michael', 'test', 'test', 'MI', '49653', 'US', 'michael@galiantsolutions.com', '(231) 256-7588', '(231) 256-5555', '', '1993', '2009', 'No Preference', '')"


Revised Code:

Code: Select all

<?php
$db_host = "localhost";
$db_user = "user";
$db_pwd = "pass";
$db_name = "avsab_registration";
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);
?>
<?php include 'states.php' ?>
<br />
<?php
function showOptionsDrop($array){
$string = '';
foreach($array as $k => $v){        
$string .= '<option value="'.$k.'"'.$s.'>'.$v.'</option>'."\n";     
}
return $string;
}

?>
<?php
error_reporting(E_ALL);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<link rel="stylesheet" href="http://avsabonline.org/avsabonline/administrator/templates/minted_one-point-five/css/template_css.css" type="text/css" />
<link rel="stylesheet" href="http://avsabonline.org/avsabonline/administrator/templates/minted_one-point-five/css/theme.css" type="text/css" />
<title>AVSAB Scientific Session</title>
<style>
body{font-family:arial;color:#333333;font-size:12px;}
.space{padding-right:20px;padding-bottom:20px;}
select{font-family:arial;color:#333333;width:100px;font-size:12px;}
input{font-size:12px;border:1px solid #cccccc;background:#eeeeee;}
a:link{color:#1E90FF;text-decoration:none;}
a:visited{color:#1E90FF;text-decoration:none;}
a:active{color:#1E90FF;text-decoration:none;}
a:hover{color:#1E90FF;text-decoration:none;}
</style>
<link rel="stylesheet" type="text/css" href="reset.css">
<link rel="stylesheet" type="text/css" href="form_style.css">
<link rel="stylesheet" type="text/css" href="form_style2.css">
<script>
function autotab(original,destination){
if (original.getAttribute&&original.value.length==original.getAttribute("maxlength"))
destination.focus()
}
</script>
</head>
<body>
<center>
<div style="width:820px;">
<table style="width:720px;"">
<tr>
<td>
<?php
if (!isset($_POST['submit'])) {
?>
<div id="container">
<form method="post" name="avsab">
<fieldset id="personal" class="repeat">
<legend>Personal Information</legend>

<div style="float:left;">
<span class="oneField">
<label class="preField">Prefix
<select name="prefix"><?php echo showOptionsDrop($prefix_arr); ?></select>
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Degree / Suffix for badge
<select name="degree"><?php echo showOptionsDrop($degree_arr); ?></select>
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Other
<input TYPE="text" NAME="otherdegree" size="20"></label>
</span>
</div>
<br><br><br><br>
<div style="float:left;">
<span class="oneField">
<label class="preField">Last (family) Name
<input TYPE="text" NAME="lastname" size="36">
<span class="reqMark">*</span></label><br></span>
</div>
<div style="float:left;">
<span class="oneField">
<label class="preField">First (given) Name
<input TYPE="text" NAME="firstname" size="36">
<span class="reqMark">*</span></label><br></span>
</div>
<br><br><br><br>
<div style="float:left;">
<span class="oneField">
<label class="preField">If Student List College(s) Attended
<input TYPE="text" NAME="college" size="40">
<span class="reqMark">&nbsp;</span></label><br></span>
</div>
<div>
<span class="oneField">
<label class="preField">Year of Graduation
<input TYPE="text" style="text-align:left;" id="gradyear" NAME="gradyear" size="15"  value=""></label>
</span>
</div>
<br /><br />
<div style="float:left;">
<style>.radio_tag{font-size:12px;}.radio_tag input{border:none;}.other_tag{font-size:12px;}</style>
<label class="preField" style="float:left;v">Dietary Preference</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="No Preference">No Preference</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Vegetarian">Vegetarian</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Vegan">Vegan</label>
<label class="radio_tag"><input type="radio" name="dietary_preference" value="Other">Other</label>
<label class="other_tag"><input TYPE="text"  name="other_dietary_preference" size="20"></label>
</div>
</fieldset>
<fieldset id="contact" class="">
<legend>Contact Information</legend>


<span class="oneField">
<label class="preField">Address
<input TYPE="text" NAME="address" size="120">
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>


<div style="float:left;">
<span class="oneField">
<label class="preField">City
<input TYPE="text" NAME="city" size="50">
<span class="reqMark">&nbsp;&nbsp;</span></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">State/Province
<select name="state"><?php echo showOptionsDrop($states_arr); ?></select></label><br></span>
</div>

<div style="float:left;">
<span class="oneField">
<label class="preField">Poastal Code
<input TYPE="text" NAME="zipcode" size="10">
<span class="reqMark">*</span></label><br></span>
</div>
<br /><br /><br /><br />
<div style="float:left;">
<span class="oneField">
<label class="preField">Country
<select name="country"><?php echo showOptionsDrop($country_arr); ?></select>
<span class="reqMark"></span></label><br></span>
</div>
<div style="float:left;">
<span class="oneField">
<label class="preField">Email
<input TYPE="text" NAME="email" size="50">
<span class="reqMark">*</span></label><br></span>
</div>
<br /><br /><br /><br />
<div id="phone" style="float:left;">
<span class="oneField">
<label class="preField">Daytime Phone 
<input type="text" name="phone_first" size=4 onKeyup="autotab(this, document.avsab.phone_second)" maxlength=3> <input type="text" name="phone_second" size=4 onKeyup="autotab(this, document.avsab.phone_third)" maxlength=3> <input type="text" name="phone_third" size=5 maxlength=4>
<span class="reqMark"></span></label><br></span>
</div>
<div id="phone" style="float:left;">
<span class="oneField">
<label class="preField">Fax
<input type="text" name="fax_first" size=4 onKeyup="autotab(this, document.avsab.fax_second)" maxlength=3> <input type="text" name="fax_second" size=4 onKeyup="autotab(this, document.avsab.fax_third)" maxlength=3> <input type="text" name="fax_third" size=5 maxlength=4>
<span class="reqMark"></span></label><br></span>
</div>
<br /><br /><br /><br />
<div style="padding-top:40px;">
<div style="float:right;">
<input type="hidden" name="year" value="2009">
<input type="submit" name="submit" value="Submit Registration">
</div>
</div>
</fieldset>
</div><!--eof container-->
</body>
</html>
<?php
}else{
?>
<?php
$phone_main = '('.$_POST['phone_first'].') '.$_POST['phone_second'].'-'.$_POST['phone_third'];
$fax_main = '('.$_POST['fax_first'].') '.$_POST['fax_second'].'-'.$_POST['fax_third'];
$prefix = $_POST['prefix'];
$prefix = mysql_real_escape_string($_POST['prefix']);
$degree = mysql_real_escape_string($_POST['degree']);
$otherdegree = mysql_real_escape_string($_POST['otherdegree']);
$lastname = mysql_real_escape_string($_POST['lastname']);
$firstname = mysql_real_escape_string($_POST['firstname']);
$address = mysql_real_escape_string($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zipcode = mysql_real_escape_string($_POST['zipcode']);
$country = mysql_real_escape_string($_POST['country']);
$email = mysql_real_escape_string($_POST['email']);
$phone = $phone_main;
$fax = $fax_main;
$college = mysql_real_escape_string($_POST['college']);
$gradyear = mysql_real_escape_string($_POST['gradyear']);
$year = mysql_real_escape_string($_POST['year']);
$dietary_preference = mysql_real_escape_string($_POST['dietary_preference']);
$other_dietary_preference = mysql_real_escape_string($_POST['other_dietary_preference']);
// Put the SQL query string together
$query = "INSERT INTO `register`
(prefix, degree, otherdegree, lastname, firstname,
address, city, state, zipcode, country,
email, phone, fax, college, gradyear,
year, dietary_preference, other_dietary_preference)
VALUES
('$prefix', '$degree', '$otherdegree', '$lastname', '$firstname',
'$address', '$city', '$state', '$zipcode', '$country',
'$email', '$phone', '$fax', '$college', '$gradyear',
'$year', '$dietary_preference', '$other_dietary_preference')";

// Perform the query
$queryResult = mysql_query($query);

// Dump the result for debugging purposes
echo '<br />The SQL query that was attempted was:<br />';
var_dump($query);
echo '<br />The queryResult resource was:<br />';
var_dump($query);

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>ACVB/AVSAB Behavior Symposium - July 10, 2009</title>
<link rel="stylesheet" type="text/css" href="reset.css">
<link rel="stylesheet" type="text/css" href="form_style.css">
<link rel="stylesheet" type="text/css" href="form_style2.css">
<script>
function autotab(original,destination){
if (original.getAttribute&&original.value.length==original.getAttribute("maxlength"))
destination.focus()
}
</script>
</head>
<body>
<div>
Thank-You. The registrant has been submitted. <a href="http://avsabonline.org/avsabonline/administrator/addon3.php">Return to Annual Meeting Registrants.</a>
</div>

<?
}
?>
</table>
</div>
</center>
</body>
</html>

Re: Send to MYSQL Database

Posted: Mon Apr 05, 2010 3:49 am
by minorDemocritus
First off, make sure you keep putting code in the [ syntax=php ] [ /syntax ] bbcode, it hurts the eyes less. :mrgreen: Please edit your posts to add those bbcode tags, so the syntax highlighter can do its magic.

SQL issue:
Now that you know exactly what the SQL query was, try running that exact query directly on the database. As I noted, phpMyAdmin is great for this (there's an SQL tab at the top that lets you run direct queries). That will (probably) give you errors that will let you figure out what's wrong.

Check the manual page for mysql_query(): http://php.net/manual/en/function.mysql-query.php
Notice that it says for INSERT querys, mysql_query() should return TRUE on success and FALSE on failure. In your updated code, the second dump is the same as the first, because you called var_dump($query) again, instead of var_dump($queryResult).
EDIT: Whoops, that's my bad. I've corrected my earlier post. Sorry about that :banghead:

Debug Snippet details:

Code: Select all

echo 'Line number ' . __LINE__ . ' was executed.<br />';
I included this snippet because of it's simplicity, and the amazing number of uses. I didn't really notice any portions of your code that could really be helped by it. It's useful in something like this:

Assume you have a form that checks the username. You are using the username 'johndoe', and password 'secret'. I use the GET method for easy presentation, you should obviously not actually use the GET method for things such as passwords!

Code: Select all

<?php
// name this script debugtest.php
$username = $_GET['username'];
$password = $_GET['password'];
if ($username == 'johndoe') {
    if ($password == 'secre') {
        echo 'Hello John!<br />';
    } else {
        echo 'Sorry, invalid user name or password!<br />';
    }
} else {
    echo 'Sorry, invalid user name or password!<br />';
}
?>
Now access debugtest.php?username=johndoe&password=secret . The script will output "Sorry, invalid user name or password!". Assume also that you overlook the typo in the password check IF statement. You can still debug the script using that code snippet:

Code: Select all

<?php
// name this script debugtest2.php
$username = $_GET['username'];
$password = $_GET['password'];
if ($username == 'johndoe') {
    echo 'Line number ' . __LINE__ . ' was executed.<br />'; // line 5
    if ($password == 'secre') {
        echo 'Line number ' . __LINE__ . ' was executed.<br />'; // line 7
        echo 'Hello John!<br />';
    } else {
        echo 'Line number ' . __LINE__ . ' was executed.<br />'; // line 10
        echo 'Sorry, invalid user name or password!<br />';
    }
} else {
    echo 'Line number ' . __LINE__ . ' was executed.<br />'; // line 14
    echo 'Sorry, invalid user name or password!<br />';
}
?>
When you access the new script with debugtest2.php?username=johndoe&password=secret , it will output this:

Code: Select all

Line number 5 was executed.
Line number 10 was executed.
Sorry, invalid user name or password!
This tells you that the script ran line 5, therefore it entered the first IF statement, so the username was correct. It further tells you that the script skipped line 7 and therefore missed the second IF statement, but ran line 10, so it entered the ELSE part of the second IF statement. This tells you that the password match did not happen.
It's obviously an oversimplified example, but the point is that the __LINE__ snippet is a great debugging tool, since it tells you if a line ran or didn't. This allows you to figure out what part of the script is being executed.

Re: Send to MYSQL Database

Posted: Mon Apr 05, 2010 12:38 pm
by mfandel
ok, thank!
Working on those changes, but I think I figured out what the issue with some surfing around, but not how to fix.

The insert is failing because the ID value has to be unique and by default every query your form generates uses the same key value (127). Since there is already an entry in the database with the same value, MySQL refuses to overwrite it.

I changed the ID value for the existing record to 100 and then tried posting through the form. Sure enough, it worked, posting a new entry with ID value 127. I tried posting again, and this time it failed.

How do I make the key value unique?

In PHPmyadmin my table structure for the id is:
Field: ID
Type: tinint(100)
Null: No
Default: None
Extra: auto_increment