Updating MySQL Fields with Quotes and Apostrophes

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";
brendan-amexAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
you may try

$query =  sprintf("UPDATE table SET col1 = '%s', Default = '%s', SortSearch = %s, Width = %s WHERE UserID = %s",
            mysql_real_escape_string($update1),
            mysql_real_escape_string($default1),
            mysql_real_escape_string($sorting1),
            mysql_real_escape_string($width1),
            mysql_real_escape_string($userID)
);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
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.
0
brendan-amexAuthor Commented:
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;
0
Dave BaldwinFixer of ProblemsCommented:
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';"
0
brendan-amexAuthor Commented:
Thanks to both of you, lots of help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.