Solved

Clean text to insert in database

Posted on 2016-10-31
9
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 57

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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 110

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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