cancel
Showing results for 
Search instead for 
Did you mean: 

Writing an apostrophe to MySQL (Magic Quotes)

SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Writing an apostrophe to MySQL (Magic Quotes)

OK, I kinda understand this but dont know how best to administer it.
Previously, writing form data to MySQL was fine even if the data was a name like O'Brien because i was using str_replace("'","''",$PlayerName)
On the PAYH platform it's failing to write to the database.
I can get_magic_quotes_gpc() to see if they are on or off and i can use \' to escape the apostrophe etc but what would be the best way to administer this problem since a number of different forms write to the database?
I guess i should write a function to check for magic quotes and then escape the apostrophe if required. Is this correct?
Any suggestions would be appreciated
7 REPLIES 7
Ben_Brown
Grafter
Posts: 2,839
Registered: ‎13-06-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

What I tend to do is run stuff through stripslashes, then mysql_real_escape_string. The stripslashes will either remove those added by magic_quotes if it is enabled, or do nothing if it isn't, so it works either way. mysql_real_escape_string will make sure any odd characters are escaped properly, not just slashes so it is better to use.
Peter_Vaughan
Grafter
Posts: 14,469
Registered: ‎30-07-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

I'll save you the trouble of writing a function...
First open database somewhere e.g.

$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error() );

Next put this function somewhere so it gets included

function escape_data($data)
{
    global $dbc; // database must be opened first
    if (ini_get('magic_quotes_gpc')) $data = stripslashes($data);
    return mysql_real_escape_string($data, $dbc);
}

Then build up the insert/update query using the above function on all fields you know may have 'special' characters you need to delimit e.g.

$order_query = "
INSERT into order_details (
created, firstname, surname, email, tel_no, tel_no2, notes )
VALUES (
'$dt_now',
'" . escape_data($_POST['firstname']) . "',
'" . escape_data($_POST['surname']) . "',
'" . escape_data($_POST['email']) . "',
'{$_POST['tel_no']}',
'{$_POST['tel_no2']}',
'{$_POST['car_reg']}',
'" . escape_data(trim($_POST['notes'])) . "' ) ";
SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

Thanks very much Peter for the very informative and useful reply
It serves me perfectly well
Ben_Brown
Grafter
Posts: 2,839
Registered: ‎13-06-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

Don't forget to sanitise all your input though, in the example Peter has given tel_no, tel_no2 and car_reg could all be used for SQL injection by sending specially crafted requests to the webserver.
SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

Thanks Ben
I don't fully understand SQL injection attacks so would appreciate your tips on 'sanatising' my SQL inputs.
Cheers
Peter_Vaughan
Grafter
Posts: 14,469
Registered: ‎30-07-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

I didn't include all the validation of those fields prior to forming the SQL statement. e.g. numeric only for phone numbers, regex validation for reg no, stripping out any unwanted strings in the names, limiting entry sizes etc.
SQL injection involves putting in specific character sequences in say the surname field if a form which could result is code being executed on the webserver to compromise it if you don't put limits on the entry sizes or what characters are allowed.
Anything going through escape_data will be safe from SQL injection.
Just google for SQL injection to see what is possible and how to stop it.
SoulBriski
Grafter
Posts: 179
Registered: ‎15-06-2007

Re: Writing an apostrophe to MySQL (Magic Quotes)

ok thanks