I want to share this i learn here.

Last week i wrote a php script where it was a form, and once the form was submitted, it would enter the information into my mysql database, but for some reason, whenever the information data contain the apostrophe character (the apostrophe character is the one that's next to the enter key on your keyboard. Which is the apostrophe key? its this one: ' )

note: this assumes you are familiar with php, mysql and html.

this is an example of the data to be entered into the database when a user would submit the form with information:

This is an example of the apostrophe not being inserted into my database when the user's information is entered


one thing i learned is that if the information was entered with the backlash character in front of the apostrophe it would work, so my input information would look like, it would work:
This is an example of the apostrophe not being inserted into my database when the user\'s information is entered


Notice i basically changed user's to user\'s

If i were to try to enter this data into my database with my form, it wouldn't work. Finally, i figured out why it doesn't work. I figured out a PHP function to allow these specials characters into the database using the INSERT or UPDATE commands.


But i know i couldn't have my visitors have to enter a \ character every time they use the apostrophe character.

I found a php function that does the trick, its called the: mysql_real_escape_string()

So to understand the mysql_real_escape_string() function, i will show you how it works in a real example:

Lets say i have a form, where i ask the user's name, once the user submits the form, his name would be entered into a database.

the name of the input variable will be called "user_name" so the HTML code would like something like this:
<form method="post" action="">
<input type="text" name="user_name" value="wallpaperama's">
<br />
<br />
<input type="submit" name="Submit" value="Submit">
</form>


In this case, the value of user_name is equal to: wallpaperama's

now when its time for the PHP to process the variable user_name and insert into the data, i would use the mysql_real_escape_string() function to make it work, and this is how i would declare it in the php script

<?php
$user_name = mysql_real_escape_string ($_POST['user_name']);
?>


Now that the variable $user_name has been declared with the real strings, i can insert it into the database and mysql will take it without any problems.

<?php
$sql = "INSERT INTO users SET username = '".$user_name."' ";
?>



UPDATE: New Version: If mysql_real_escape_string() is not working try mysqli_real_escape_string()

PHP has updated this function, it is now called mysqli_real_escape_string()

TIPS: you can also try addslashes() function. but if you are entering data into your MySQL database, you should use mysqli_real_escape_string() instead.

why does all this happen? it is because of this thing called magic_quotes. if your server has magic quotes enabled, it automatically adds the slash to your data so your script will not error out. but if you dont have magic quotes enabled, you will have to use mysqli_real_escape_string(). so if the magic_quotes_sybase is turned on in your php.ini configuration, no backslashes are stripped out, but two apostrophes are put instead.


mysql_real_escape_string = Escapes special characters in a string for use in a SQL statement

Description
string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] )

if you want more help on this function, you can visit the official PHP website at for really helpful information.
http://www.php.net/manual/en/function.mysql-real-escape-string.php
http://www.php.net/manual/en/function.addslashes.php

Thanks and i hope it helps, i tried everywhere to find this, but no one would give me information because i didn't know how to explain it.

leni