?
Solved

Clean text to insert in database

Posted on 2016-10-31
9
Medium Priority
?
94 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 58

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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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 2000 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 111

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

771 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