Clean text to insert in database

What is the best way to clean text before inserting into a database?

MySQL server version for the right syntax to use near 've done a test to see if this works.',Now())' at line 2

Inserting "I've".
JeffAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris StanyonConnect With a Mentor Commented:
OK. If you're only concerned with escaping characters and not security, then go with:

$Comments =  mysqli_real_escape_string($_REQUEST['Comments']);
$sql = "INSERT INTO Table (Comments) VALUES ($Comments)";

The fact that you're inserting User Data straight from the REQUEST array should be a security concern, but that's your call. What you should do is something like (assuming $mysqli is your DB handle):

$stmt = $mysqli->prepare("INSERT INTO yourTable (Comments) VALUES (?)");
$stmt->bind_param("s", $_POST["comments"]);
$stmt->execute();

Open in new window

That will take care of escaping the data and protecting you from SQL Injection. It also explicitly uses the POST array, rather than REQUEST (advisable if you're pushing data into your application)
0
 
Julian HansenCommented:
you need to escape the '

we do this by putting a backslash (\) in front of it
INSERT INTO table (myname) VALUES('Fred\'s Smittyy');

Open in new window

0
 
JeffAuthor Commented:
Then I should use addslashes()

$comments = addslashes($_POST['comments'];
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Chris StanyonCommented:
Hmm! addslashes() is not really suitable for inserting user data into a database. At the very least you should use the DB specific methods, such as mysqli_real_escape_string().

Preferably though, you should be using parameterised queries. Not only will that escape your data, it will prevent SQL Injection attacks, which is a very real concern when you're dealing with User Input.

For mySQL, you can do parameter queries with both the mySQLi and PDO drivers (I'm really hoping you're using at least one of those!)
1
 
JeffAuthor Commented:
Chris, I am using mySQLi. This is some old code and I just need to figure the simplest way to deal with special characters ( ' and " ). I did not anticipate needing this (I should have) and just need to fix this issue without having to rewrite the code.

Which is the best option?
$Comments =  addslashes($_REQUEST['Comments']);

$Comments =  mysqli_real_escape_string($_REQUEST['Comments']);

$sql = "INSERT INTO Table (Comments) VALUES ($Comments)";

mysqli_query($conn,$sql)

Thanks, Jeff
0
 
JeffAuthor Commented:
OK Chris,

I have been looking at prepared statement and bind_param all evening and I think I've got a handle on them. Not as complex as I first thought. It doesn't look like it will be too hard to convert queries as they need to be updated. And I will start using parameter queries in all my new code.

Sometimes I just want the simple answer and experts like you and Ray, and others, push us (me) to write good code and I appreciate that.

Challenge is good, and if I get stuck I always have you to fall back on!

Thanks

BTW, do you have any suggested reading or videos to help me as I get started?
0
 
Chris StanyonCommented:
Excellent. It can often take a little while for new ideas to sink in, as we all get stuck in doing things the same way we've always done them. Once you get your head around it, your programs will be much more secure and robust, often quicker and certainly easier to maintain and manage.

I often find the PHP website a great place to start when learning the language. Here are the 2 pages for mySqli prepare() and bind_param():

http://php.net/manual/en/mysqli.prepare.php
http://php.net/manual/en/mysqli-stmt.bind-param.php

These pages will give you all the info on how the methods work. Then it's just a case of applying that to your own needs. There are plenty of good tutorials on the net - here's one that gives a good overview:

http://forum.codecall.net/topic/44392-php-5-mysqli-prepared-statements/

And as you said, if you get stuck with anything, you can always ask at ExpertsExchange :)
0
 
Ray PaseurCommented:
any suggested reading...
I came across this question more or less by accident because I only follow the PHP Zone, and this was only posted in the MySQL Zone.  You can use more than one zone, and that will always get more eyes on your question.

Here's an article that might be worth a read.  It has tested-and-working code snippets that demonstrate the concepts.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
JeffAuthor Commented:
Thanks Ray. I thought I had posted it in both MySQL and PHP. I always appreciate your input. Thanks for the link.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.