Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Updating MySQL Fields with Quotes and Apostrophes

Posted on 2011-10-24
Medium Priority
Last Modified: 2012-05-12
I'm trying to word my SQL query so that it won't fail when someone decides to input either a quote or an apostrophe. The problem is that I think I have to either put quotes or apostrophes around my query for it to function properly.

The user enters a value and it should be able to add to the database regardless of what they enter. How can I accomplish this?

$update and $default are the variables. Thank you.

Here's what I have for the query:

$query = "UPDATE table SET col1 = '$update1', Default = '$default1', SortSearch = $sorting1, Width = $width1 WHERE UserID = $userID";
Question by:brendan-amex
  • 2
  • 2
LVL 61

Accepted Solution

HainKurt earned 1400 total points
ID: 37020148
you may try

$query =  sprintf("UPDATE table SET col1 = '%s', Default = '%s', SortSearch = %s, Width = %s WHERE UserID = %s",
LVL 84

Expert Comment

by:Dave Baldwin
ID: 37020391
Note that you have to make the connection to the MySQL server Before you run HainKurt's code.  mysql_real_escape_string() is the recommended method.

Author Comment

ID: 37020683
Great, so then how would use it on a query coming back the other way? When I try to run a SELECT query in order to populate it into an HTML form, the form is blank when I use quotes. Can I use it this way as well?

SELECT Field1, Field2 FROM table WHERE UserID = $userID;
LVL 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 600 total points
ID: 37020730
No, if you use mysql_real_escape_string() when you do the INSERT or UPDATE, you shouldn't need it (and it wouldn't do anything) when you SELECT it.  You still have to single quote string variables in your SQL statments.  If UserID is a string then:

"SELECT Field1, Field2 FROM table WHERE UserID = '$userID';"

Author Closing Comment

ID: 37020869
Thanks to both of you, lots of help.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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…
Suggested Courses

572 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