–i-am-a-dummy

This article describes a single MySql function to sanitize strings.
The provided options are:

• replace multiple spaces to a single space
• remove Unix and Windows linebreaks and replace them with a space
• remove tabs and replace them for a single space
• remove multiple backslashes
• completely remove htmlentities
• replace htmlentities with the corresponding character
• remove all HTML tags

All code is available in a zip file.

Where do you stop verifying and cleaning your data?
The question of where your data verification and cleaning should be done is too application-specific to have one right answer.
Only relying on client-side scripting is definitely the wrong answer.
Verifying the data for SQL injection, valid numbers and dates and formats should definitely be done on the server side before it is send to your database.
Cleaning user input on the server side should ideally also be done before the data is sent to the database.
As a DBA, the question rises if your can really rely on it? And if so, what about cleaning migrated data or cleaning up legacy data from a predecessor?

You can use this MySql function to clean up strings before inserting the string in a table.
Because this is a single function that can be used in different scenarios it has a lot of parameters to customize the output.
Feel free to remove some functionality from this function to limit the number of parameters.
Alternatively you can use a wrapper function to call this function without parameters. e.g.:

Install the function in your database:
The functionality to html-UnEncode a string is done by calling another function. The credits for this function goes to Charlie Armor.
You can find it here: http://forums.mysql.com/read.php?98,246527,246527 and I have included it in the zip file because my code relies on it.
Unzip the file and on the mysql prompt:

This assumes both files are in the same directory where you have started the mysql client.

Using the function:
The function is called str_clean() and has eight parameters:

p_text              i         string : the text that needs to be cleaned
p_remove_multiple_blanks      Boolean: replace multiple spaces to a single space
p_remove_linebreaks           Boolean: remove Unix and Windows linebreaks and replace them with a space
p_remove_tabs                 Boolean: remove tabs and replace them for a single space
p_remove_multiple_backslashes Boolean: remove multiple backslashes
p_remove_htmlentities         Boolean: completely remove htmlentities
p_html_unencode               Boolean: replace htmlentities with the corresponding character
p_remove_html                 Boolean: remove all HTML tags

This is also documented in the code.

Now let’s clean up a string:

Note: The html is messing up the ampersand on line with the htmlUnencode.

You can download the functions in this zip file: str_clean_2012-06-02.zip

What kind of data cleanup do you use in your database application?

Related posts

• Mysql: creating a link to your glossary while fetching text for a webpage (2)
• MySql Lorum Ipsum generator (1)
• Howto generate meaningful test data using a MySQL function (2)
• Manage hierarchical data with MySQL stored procedures (6)
• Create random test data using real data from your MySql database. (1)

Tags: cleanup data, download code, functions, MySQL

This entry is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.