Solved

Clean text to insert in database

Posted on 2016-10-31
9
73 Views
Last Modified: 2016-11-05
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".
0
Comment
Question by:Jeff
9 Comments
 
LVL 55

Expert Comment

by:Julian Hansen
ID: 41867296
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
 

Author Comment

by:Jeff
ID: 41867442
Then I should use addslashes()

$comments = addslashes($_POST['comments'];
0
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 41867474
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Jeff
ID: 41867712
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
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 41867731
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
 

Author Comment

by:Jeff
ID: 41867904
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
 
LVL 43

Expert Comment

by:Chris Stanyon
ID: 41868108
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
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41875298
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
 

Author Comment

by:Jeff
ID: 41875537
Thanks Ray. I thought I had posted it in both MySQL and PHP. I always appreciate your input. Thanks for the link.
1

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL: Updating SubQuery Match Faster 9 53
Have issues with Query MySQL 9 72
Determining creation & modification dates on MySQL tables 4 45
issue with DB import 1 19
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question