How to clean form input for SQL (apostrophes, symbols)

BhoscaCapall

I have a form coded in HTML, which uses PHP to insert the data to a MySQL table.

Apostrophes, currency symbols and I presume slashes all screw it up.

How can I fix this?

Bear in mind that I don't simply want to strip these characters, as I will need them for output.

Colonel Panic

Use mysql_real_escape_string to escape potentially evil stuff without stripping out anything.

BhoscaCapall

Thanks, that sorts the apostrophes out, however I still get grief with the currency symbols

I tried "€10 on the door / £8 before" as a test and this is what appeared in my table:

€8 10n the door / £8 before

Do I need to manually code it to replace £ with £ etc or is there an inbuilt function?

Deliverance XXV

BhoscaCapall wrote: »

Thanks, that sorts the apostrophes out, however I still get grief with the currency symbols

I tried "€10 on the door / £8 before" as a test and this is what appeared in my table:


Do I need to manually code it to replace £ with £ etc or is there an inbuilt function?

This is something I wondered about myself until I outputted it on a webpage and it outputted perfectly anyway.

CreepingDeath

The standard approach for cleaning SQL entry is to use prepared statements.
This will also protect you from SQL injection attacks which expose your website to hackers.

PHP Prepared Statement

OneIdea

BhoscaCapall wrote: »

Thanks, that sorts the apostrophes out, however I still get grief with the currency symbols

I tried "€10 on the door / £8 before" as a test and this is what appeared in my table:


Do I need to manually code it to replace £ with £ etc or is there an inbuilt function?

You could try this for now:
http://www.php.net/manual/en/function.htmlentities.php
[PHP]
mysql_real_escape_string(htmlentities($inputData));
[/PHP]

It will convert, example currency symbols in to html codes before they enter your database, and thus they will display correctly when printed out to the page.