Updating MySQL Fields with Quotes and Apostrophes

Posted on 2011-10-24
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
    LVL 51

    Accepted Solution

    you may try

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

    Expert Comment

    by:Dave Baldwin
    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

    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 82

    Assisted Solution

    by:Dave Baldwin
    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

    Thanks to both of you, lots of help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    761 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

    12 Experts available now in Live!

    Get 1:1 Help Now