Protecting MySQL Injection from PHP form
| From: | Jason Pruim | Date: | Thu, 24 May 2007 16:10:19 +0000 |
| Subject: | Protecting MySQL Injection from PHP form | ||
| Groups: | php.general | ||
| Request: | Send a blank email to php-general+get-255542@lists.php.net to get a copy of this message | ||
Hi Everyone,
I think I have found away to prevent SQL code injection into my database and just wanted to get everyones comments on my method.
<?PHP
include 'defaults.php';
// Setting error message variable to nothing
$errmsg = "";
// Check form info
if (!isset($_POST['FName']) || empty($_POST['FName'])) $errmsg .="<P>Please enter your name</P>";
if (!isset($_POST['LName']) || empty($_POST['LName'])) $errmsg .="<P>Please enter your address</P>";
if (!isset($_POST['Add1']) || empty($_POST['Add1'])) $errmsg .="<P>Please enter your city</P>";
if (!isset($_POST['City']) || empty($_POST['City'])) $errmsg .="<P>Please enter your state</P>";
if (!isset($_POST['State']) || empty($_POST['State'])) $errmsg .="<P>Please enter your zip code</P>";
if (!isset($_POST['Zip']) || empty($_POST['Zip'])) $errmsg .="<P>Please include your zipe code</P>";
if (!isset($_POST['subName']) || empty($_POST['subName'])) $errmsg .="<P> Please enter your submitter name</P>";
// Tell script what to do if there is a error message
if ($errmsg!= "") {
echo $errmsg;
echo "<a href=\"javascript:history.back();\">Please go back and fill out the missing fields</a>";
exit;
} else {
echo "<p>success: all fields were filled out</p>";
}
$FName= $_POST['FName'];
$LName= $_POST['LName'];
$Add1= $_POST['Add1'];
$Add2= $_POST['Add2'];
$City= $_POST['City'];
$State= $_POST['State'];
$Zip= $_POST['Zip'];
$Date= $_SERVER['REQUEST_TIME'];
$Record= "\t";
$subName= $_POST['subName'];
$subEmail= $_POST['subEmail'];
$subPhone= $_POST['subPhone'];
$chkMember=serialize($_POST['chkMember']);
$chkAdd=serialize($_POST['chkAdd']);
$chkDel=serialize($_POST['chkDel']);
echo "$FName First Name<BR>";
$link = mysqli_connect($server, $username, $password, $database)
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully <BR>';
//mysqil_select_db('legion') or die('Could not select database' . mysql_error());
echo 'DB selected <BR>';
//Create the statement
$stmt = mysqli_prepare($link, "INSERT INTO current VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
mysqli_stmt_bind_param($stmt, 'sssssssssssssss', $FName, $LName,$Add1, $Add2, $City, $State, $Zip, $Date, $Record, $subName, $subEmail, $subPhone, $chkMember, $chkAdd, $chkDel);
//Add the record
mysqli_stmt_execute($stmt);
printf("%d Row Inserted.\n", mysqli_stmt_affected_rows($stmt));
//Close the statement
mysqli_stmt_close($stmt);
?>
The reason I am echoing $FName just above the link was a debugging tool to make sure that it was filling at least SOME of the field properly before inserting them into the database.
Also, just for planning purposes, I do plan on adding authentication to the form (Which I think I can do and track with sessions) but I have alot more reading to do before I can get that right.
One other thing... What I did to test it was in the $FName field I typed: "Jason"; "SELECT * FROM table"; and it inserted the value, but didn't execute the code, Am I correct in thinking that I'm fairly safe from the bad people and the stupid users?