SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Prepared Statement + mysqli_real_escape_string??

    Is there a way to use mysqli_real_escape_string so that I can effectively escape single and double quotes from a data entry form before being inserted into MySQL, PLUS get the added benefits of a Prepared Statement?


    Debbie

  2. #2
    SitePoint Enthusiast Wuiqed's Avatar
    Join Date
    Dec 2006
    Location
    Sweden
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's no need for the mysql escape function when you're using prepared statements.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    yeah… if your using that function with prepared statements where there should be a separation between SQL and user controlled data your not doing things correctly.
    The only code I hate more than my own is everyone else's.

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    yeah… if your using that function with prepared statements where there should be a separation between SQL and user controlled data your not doing things correctly.
    If I wanted to insert this into MySQL...

    <p>Debbie's ride to work was late today, so she grabbed the bus.</p>
    <p>It turns out the problem was that Mike's car had a flat tire.</p>
    How would a Prepared Statement handle the single quotes?

    Without escape characters it would break.

    With mysqli_real_escape_string it would be fine.


    Debbie

  5. #5
    ¬.¬ shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    Prepared Statements does not need to handle the single quotes. The SQL query and the data never mix. They are handled independently of one another. Understand, escape_string escapes those things that have meaning in an SQL query, however, if the data is never part of an SQL query it doesn't need to escape those.

    You do not use escape_string with prepared statements.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by logic_earth View Post
    Prepared Statements does not need to handle the single quotes. The SQL query and the data never mix. They are handled independently of one another. Understand, escape_string escapes those things that have meaning in an SQL query, however, if the data is never part of an SQL query it doesn't need to escape those.

    You do not use escape_string with prepared statements.
    So for my needs, I don't need to use Prepared Statements, but I will need mysqli_real_escape_string to escape ' and " in my HTML markup, right?


    Debbie

  7. #7
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysqli_real_escape_string gives a hint about it's purpose with the first word of the function name (it for MySQL, not HTML).

    It'll escape ' into \' which is still going to look like \' in your HTML.
    It should become & #039; Use htmlentities
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  8. #8
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    A prepared statement will automatically do a normal SQL comment of single quotes (if you tell the engine you use double quotes instead of single quotes for strings, it will do the same with those etc).

    If you take a look on the example below, this is how your string would look "internally" after you bind it to the prepared statement. It will add enclosed single quotes since you tell its a string, and any single quotes inside the string will get one more appended to it.

    However when the insert is completed, it would look exactly the same as the string you originally had.

    Example:
    '<p>Debbie''s ride to work was late today, so she grabbed the bus.</p>'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •