asked on

Using php to filter out the text before insert into MySQL DB

Hi experts,

I was working with the mysql and php which I need to insert data into mysql via php. I've noticed that my data was weird that I wasn't able to insert the data correctly into the mysql. Later I found out that there were some characters in the data that caused this. I was wondering what would be a better solution or a better way to resolve these if I see:
1. html tags (do I need use htmlspecialchars or htmlentities to convert these then store into database)
2. dingbats like trademarks, copyright, register (how can I resolve those or filter those with php in order for me to insert the correct converted data)

Thanks

gr8gonzo

We'd have to see your code to know for sure, but my guess is that you're not using mysqli_real_escape_string() on your values.

See the documentation for some examples:
https://www.php.net/manual/en/mysqli.real-escape-string.php

The best way to do inserts is to use prepared statements. It takes a tiny bit more code but it should eliminate these kinds of issues and also provide protection against SQL injection.

Examples:
https://www.php.net/manual/en/mysqli.prepare.php

ASKER CERTIFIED SOLUTION

gr8gonzo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

lenamtl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kinderly Wade

ASKER

Hi all,

Thanks for the great feedbacks. I was trying to upload the product description field from the local server up into the website. Somehow I found out that the product description field not only contain dingbats and other characters, the field also contained some html tags like div with bootstrap classes. This kind of threw me off because the format did affect the way it looked on the web as well and for those unicode (thanks gr8gonzo for the post) that I needed to deal with. I was using PDO library and I do the prepare statement along with begintransaction, commit, rollback etc...

I am wondering if there is a library in PHP that can do auto convert for those unicode chars or dingbats and either strip the html tags which I can store the data in just text format (or a format that will not cause issue ) when I insert the data into mysql DB? Thanks

SOLUTION

skullnobrains

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lenamtl

There is no one script solution, if you read my answer
https://www.experts-exchange.com/questions/29145008/Using-php-to-filter-out-the-text-before-insert-into-MySQL-DB.html?anchorAnswerId=42858463#a42858463
and take the time to check everything you will understand why this is cannot one script solution.

You need to check the page, the scripts, the DB connction, DB itself ect...

One quick solution is to not allow html tag and dingbat to get saved in DB.
Or you can use TinyMCE template feature or raw option.
https://www.tiny.cloud/docs/plugins/template/
https://www.tiny.cloud/docs-3x/reference/configuration/Configuration3x@entity_encoding/

skullnobrains

that answer starts by pooring iso-8859-1 into an utf-8 db...

Kinderly Wade

ASKER

Thanks all. I just pulled some data from web as now to filter the dingbats and I am doing a string filter to remove all html tags that weren't suppose to be in the text field. Now it works good!