0

I'm trying to enter some texte in my database, text wh'os entered by users. However I'm having an issue with some special characters, namely "&" and "+".

When the text involves a &, the sentence is chopped and nothing after that character will be added in the db. For the +, it makes the whole entry null.

I don't get it, becaue I'm escaping the special characters with mysqli_real_escape_string

Here's the query:

$texte=mysqli_real_escape_string($texte);
$bdd->query('INSERT INTO  messages (idconversation, texte, id_emeteur, id_recepteur, invitation) VALUES ('.$idConversation.', \''.$texte.'\',\''.$idEmeteur.'\',\''.$idDestinatere.'\',\''.$invite.'\')');

Any idea? :/

EDIT! (first, all the data that's inserted in the databae is served generated and not coming from the user, except the $text variable)

Thakns to your advice, I've investiguated the other steps in the process. It looks like it's got nothing to do with mysql or php. Earlier in the process, the data is transfered using ajax. Here's the code

xhr.open("POST", "index.php?page=repondreMessage", true);
xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhr.send("message="+nouveaumessage+"&pseudo="+destinataire+"&tickbox="+tickbox);

(th data is here in "message"). How should i process the data so it can safely be conveyed in the request?

Rouli
  • 137
  • 1
  • 10
  • possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Álvaro González Feb 03 '14 at 18:00
  • You've obviously escaped one of the many things you're putting in here, but what about the rest? When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **Avoid** using string interpolation to accomplish this. – tadman Feb 03 '14 at 18:36
  • If you use prepared statements, you would not have to deal with this escaping nightmare. – Andy Lester Feb 03 '14 at 21:53

3 Answers3

2

There are NO issues neither with & nor with + characters in mysql.

Your problem is that you are using whole software complex including many scripts and layers but for some reason blame mysql only. You have to sort out different layers and verify data entering and exiting each of them. This way you will find a layer to blame.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
2

You are going to have much bigger problems than & and + - you are wide open to SQL injection if any of the other data is user data.

As Your Common Sense pointed out, there are no known issues with & or + in a string in MySQL. You need to double-check that the data is what you expect. Try echo $texte; -- you will almost certainly find characters there that you didn't expect.

My best guess as to your actual problem: You're using data that isn't properly encoded in $_GET (or maybe) $_POST. For example, in this scenario,

http://mydomain.com/?texte=foo + bar

$_GET['texte'] is not foo + bar; it's foo bar, because + is treated as a space in a URL. In this example,

http://mydomain.com/?texte=foo &bar

$_GET['texte'] is not foo &bar; it's foo, because & is treated as a parameter separator; the server thinks you have another parameter bar (with no value).

elixenide
  • 44,308
  • 16
  • 74
  • 100
  • Hi! Thanks for answering! Looks like you were right, it's likely a POST/GET problem. Thing is, the data is transfered through ajax. her's the request: xhr.open("POST", "index.php?page=repondreMessage", true); xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded"); xhr.send("message="+nouveaumessage+"&pseudo="+destinataire+"&tickbox="+tickbox); It's the variable message that holds my data at this point. Any insight on how I can convey it safely? – Rouli Feb 04 '14 at 13:00
  • You need to encode it. You can do it with a simple command like `nouveaumessage = nouveaumessage.replace('+', '%2B').replace('&', '%26');` Then, just reverse the replacements when the data hits your server. – elixenide Feb 04 '14 at 14:14
0

first of all mysqli_real_escape_string() accepts two parameter. you have only one. 2nd mysqli_real_escape_string wont escape any + or &. You should check your parsing of variables

Vivek
  • 1,446
  • 18
  • 27