HTMLHelp Forums > Apostrophe Problems

HTMLHelp Forums > Programming > Databases

jamesjohnson88

Apr 30 2009, 09:23 AM

Whenever a form is submitted to my DB, if it has an apostrophe in it my MySQL throws an error. This is the code -

QUOTE

$con = mysql_connect("localhost","u08105199","edited");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("u08105199", $con);
// Get values to insert.
$sql="INSERT INTO contactMe (FirstName, LastName, Age, Email, Comments)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]','$_POST[email]','$_POST[comments]')";

if (!mysql_query($sql,$con)) //Function is called.
{ // IF statement to check for errors.
die('Error: ' . mysql_error()); // Error checking feedback.
}
else
{
echo ("Thank you, your comment has been added."); // Feedback message.
}
mysql_close($con)

I know I have to do something with escape strings but I just can't get my head around what parts of the code needs changing.

Any help is appreciated.

Brian Chandler

Apr 30 2009, 10:13 AM

http://jp2.php.net/manual/en/function.mysq...cape-string.php

Actually I use mysql_escape_string() but the "real" thing is supposed to be "better". You need to apply this to any input value that may include apostrophes (etc).

(As it stands, your script is totally open to SQL injection: I supply a comments field that starts

'); <...now I can write any SQL command I want to run on your server.

It is a much better approach _always_ to check input strings before letting them near your database.

jamesjohnson88

Apr 30 2009, 10:22 AM

Was told to try this -

//set variables
$firstname = mysql_real_escape_string($_POST['firstname']);
$lastname = mysql_real_escape_string($_POST['lastname']);
$comments = mysql_real_escape_string($_POST['comments']);
// set query
$sql="INSERT INTO contactMe (FirstName, LastName, Age, Email, Comments)
VALUES ('$firstname', '$lastname', '$comments')"

Still not working for me.

jamesjohnson88

Apr 30 2009, 10:39 AM

Got it working, thanks for the help.

geoffmerritt

May 1 2009, 10:13 AM

QUOTE

Actually I use mysql_escape_string() but the "real" thing is supposed to be "better". You need to apply this to any input value that may include apostrophes (etc).

What i have read, they do the same, the "real" only works after you are logged into the db. So if you need to apply to the value prior to logging into the db, mysql_escape_string() will be the only option.

QUOTE

It is a much better approach _always_ to check input strings before letting them near your database.

The code below will check the input, and if it doesnt match what you are expecting will stop the script. the regular expressions can be tailored to suit your needs.

CODE

$input = $_POST['input'];

$inputpattern = '/^[0-9A-Za-z]+$/';

$errormessage = " field, has invalid information and needs to be changed.";

if (!preg_match("$inputpattern", "$input"))
  {
  echo "Your Input $errormessage";
  exit();
  }

I always do a client side and server side check of the script, and it is wrong to assume that all the information will come from your website form.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.