Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Writing an apostrophe to MySQL (Magic Quotes)
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Everything else
- :
- Re: Writing an apostrophe to MySQL (Magic Quotes)
Writing an apostrophe to MySQL (Magic Quotes)
28-12-2008 12:05 AM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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
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
Message 1 of 8
(1,927 Views)
7 REPLIES 7
Re: Writing an apostrophe to MySQL (Magic Quotes)
29-12-2008 4:08 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
Message 2 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
29-12-2008 5:16 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
I'll save you the trouble of writing a function...
First open database somewhere e.g.
Next put this function somewhere so it gets included
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.
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'])) . "' ) ";
Message 3 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
30-12-2008 2:34 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Thanks very much Peter for the very informative and useful reply
It serves me perfectly well
It serves me perfectly well
Message 4 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
30-12-2008 4:23 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
Message 5 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
30-12-2008 5:29 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
Thanks Ben
I don't fully understand SQL injection attacks so would appreciate your tips on 'sanatising' my SQL inputs.
Cheers
I don't fully understand SQL injection attacks so would appreciate your tips on 'sanatising' my SQL inputs.
Cheers
Message 6 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
30-12-2008 5:45 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
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.
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.
Message 7 of 8
(582 Views)
Re: Writing an apostrophe to MySQL (Magic Quotes)
30-12-2008 6:00 PM
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Report to Moderator
ok thanks
Message 8 of 8
(582 Views)
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Plusnet Community
- :
- Forum
- :
- Help with my Plusnet services
- :
- Everything else
- :
- Re: Writing an apostrophe to MySQL (Magic Quotes)