database doesn't allow special chars even after using mysqli_real_escape_string

Question

I have found lots of question like this at SO and followed the solution,but didn't work in my case.

I want to insert + into database,so I have used

$tex5=mysqli_real_escape_string($connect_dude,$_POST['text5']);

But this doesn't insert +.It gives empty space(whitespace) in database.

Please be informed,I'm using prepared statement and parameterized query.Is it the reason why database doesn't allow +?If yes,then how can I fix this?

Thanks for your time.

Code

Javascript

//call con_edi() on first button clicked.insert email on text field and click button would fire off change5()    

function con_edi(){
   document.getElementById("alu").innerHTML="<input type='text'  id='taxi' style='border:1px solid black;'><input type='submit' id='' onclick='change5(taxi.value)' value='change'>";
 }

function change5(s){
   var form=s;
   if(!form.match(/^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$/)){
      alert("Wrong Email");
   }else{
     //document.write(form);  /*for testing the output*/
     var ajax=new XMLHttpRequest();
     ajax.open("post","contact.php",true);
     ajax.setRequestHeader("content-type","application/x-www-form-urlencoded");
     ajax.onreadystatechange=function(){
       if(ajax.readyState == 4 && ajax.status == 200){

          location.reload();

        }
      }

    ajax.send("text5="+form);
   }
 }

PHP

$tex5=mysqli_real_escape_string($connect_dude,$_POST['text5']);
$query6="UPDATE `$database`.`header` SET email=?  WHERE user_id=?";

mysqli_stmt_prepare($stmt1, $query6);
mysqli_stmt_bind_param($stmt1, "si", $tex5, $logged_id);
mysqli_stmt_execute($stmt1);

Answer 1

You need to properly URL-encode the value that you are sending.

Right now, you are sending text5=lkf+alu@craftwebart.com – and in this URL context, + is the “replacement” character for a space character. That is why your script was inserting the space char into the database, not because there was something wrong with the insertion into the database per se. (Although, as mentioned, using mysqli_real_escape_string in combination with prepared statements is wrong. mysqli_real_escape_string is used to mask special characters that are inserted into the SQL syntax directly, so that they can not be confused with the actual SQL syntax. When using prepared statements with placeholders however, SQL command and data are send to the database separate from each other, so that danger does not exist in this context.)

So, you need to use

ajax.send("text5="+encodeURIComponent(form));

to send your data to the server. encodeURIComponent takes care of encoding the data properly for the URL context. It will make the + character into %2B, so that it can be send in that context safely, and PHP will decode it back to a + automatically.