Hello. Can anyone tell me how to insert special characters into a MySQL database? I've made a PHP script which is meant to insert some words into a database, although if the word contains a ' then it wont be inserted.
I can insert the special characters fine when using PHPmyAdmin, but it just doesn't work when inserting them via PHP. Could it be that PHP is changing the special characters into something else? If so, is there a way to make them insert properly? Thanks!
views:
507answers:
7
+6
A:
Are you escaping? Try the mysql_real_escape_string() function and it will handle the special characters.
zaf
2010-04-06 10:23:09
+1 for beating me to it. I've found I get more votes when I use the backtick to format my inline code, if that helps.
Anthony
2010-04-06 11:23:06
Sympathy upvote since you answered first but didn't give an example. As if this was a question that couldn't have been answered faster using PHP.net
dscher
2010-04-06 12:35:10
@dscher Thanks! If there were only more people like you! ;)
zaf
2010-04-06 12:52:42
+4
A:
$insert_data = mysql_real_escape_string($input_data);
Assuming that you have the data stored as $input_data
Anthony
2010-04-06 10:24:09
+2
A:
You are most likely escaping the SQL string, similar to:
SELECT * FROM `table` WHERE `column` = 'Here's a syntax error!'
You need to escape quotes, like follows:
SELECT * FROM `table` WHERE `column` = 'Here\'s a syntax error!'
mysql_real_escape_string() handles this for you.
Paul Lammertsma
2010-04-06 10:25:31
+5
A:
use mysql_real_escape_string
So what does mysql_real_escape_string do?
This PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.
Please inform yourself about sql_injection. You can use this link as a start
Peter Parker
2010-04-06 10:26:34
Yet another stupid article whose author has no clue. The same old story about drop tables (good to scare newbies, yes, but never work though), same stupidQuery function that look after magic quotes. And not a word about non-string values, as usual. and greatest protaction of $badWords ever
Col. Shrapnel
2010-04-06 10:35:16
ok, it is not the best article, but it should familiarize with the basics(I digged it from a fast google search) You can give a link to a better article if you have.
Peter Parker
2010-04-06 10:46:34
Well I wrote some, it the form of SO answer. http://stackoverflow.com/questions/2589820/is-it-necessary-to-use-mysql-real-escape-string-when-magic-quotes-gpc-is-on
Col. Shrapnel
2010-04-07 05:12:32
+4
A:
You are propably pasting them directly into a query. Istead you should "escape" them, using appriopriate function - mysql_real_escape_string, mysqli_real_escape_string or PDO::quote depending on extension you are using.
Krzysztof Sikorski
2010-04-06 10:26:50
+2
A:
Note that as others have pointed out mysql_real_escape_string() will solve the problem (as will addslashes), however you should always use mysql_real_escape_string() for security reasons - consider:
SELECT * FROM valid_users WHERE username='$user' AND password='$password'
What if the browser sends
user="admin' OR (user=''"
password="') AND ''='"
The query becomes:
SELECT * FROM valid_users
WHERE username='admin' OR (user='' AND password='') AND ''=''
i.e. the security checks are completely bypassed.
C.
symcbean
2010-04-06 11:13:59