Solved

Clean text to insert in database

Posted on 2016-10-31
9
39 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:jeffey48
9 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
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:jeffey48
Comment Utility
Then I should use addslashes()

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

Expert Comment

by:Chris Stanyon
Comment Utility
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
 

Author Comment

by:jeffey48
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
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:jeffey48
Comment Utility
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 42

Expert Comment

by:Chris Stanyon
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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:jeffey48
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now