How to store and retrieve special characters on mysql database

In this quick tutorial I’m going to show you how to save special characters in a mysql database. Which we will later on try to retrieve.

First, setup a testing folder then create a new php file. Also include your database configuration file on the new file that you created.

Next, copy the following code into your file. Make sure to change ‘specialchars.php’ to the name of the file that you are using:

<form action="specialchars.php" method="post">
 <input type="text" name="specar">
 <input type="submit">
</form>

The text field with the name of ‘specar’ is the text field where we are going to paste the special character.

image

Then the code which will process, save, and retrieve the special character:

<?php
if(!empty($_POST)){
$id = htmlentities($_POST['x']);
$unique_key = substr(md5(time()), 0, 5);
$create = $db->query("INSERT INTO sys_users SET UserID='$unique_key', strPassword='$id'");

$selector = $db->get_var("SELECT strPassword FROM sys_users WHERE UserID='$unique_key'");
echo html_entity_decode($selector);
}
?>

Yup, the table that I’m using is not in any way related to the data that were trying to store. But don’t mind that. You’re here to learn something on saving and retrieving special characters.

So what we did in the code is to first check whether we have any post data to process. So our script only executes if something is actually submitted to the server. Then we used a function called htmlentities. What it does is to convert a special character into an html entity. An html entity is basically a representation of a special character in terms of html.

 

Why convert?
We need to convert a special character into an html entity because you might run into problems later on if you store these kinds of characters as it is. I haven’t dug so much as to what problems might occur. Querying those values later on might produce some unexpected errors. Like an error in your sql query.

And yes, without using htmlentities. It works fine:

<?php
if(!empty($_POST)){
$id = $_POST['x'];
$unique_key = substr(md5(time()), 0, 5);
$create = $db->query("INSERT INTO sys_users SET UserID='$unique_key', strPassword='$id'");

$selector = $db->get_var("SELECT strPassword FROM sys_users WHERE UserID='$unique_key'");
echo $selector;
}
?>

 

Decoding
After saving the htmlentity to the database, we then retrieve it using the unique key that we assigned earlier. The unique key is pretty much very unique in the sense that it uses the unix timestamp as the primary source of data, then it encrypted it using the md5() function. Then we reduce the whole string into just 5 characters by using the substr() function.

To decode the html entity that was saved on the database, we used a function called html_entity_decode. And it does pretty much what its name says it does. It only requires a single argument which is the actual html entity to be decoded.

 

Conclusion
That’s pretty much what I have for this tutorial. To add a bit of security, you might want to include this function. And just call it whenever you feel like a character that you’re trying to save might ruin your query.

function clean($str){
    return trim(mysql_real_escape_string($str));
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: