Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder crmpicco's Avatar
    Join Date
    Jan 2005
    Location
    Mauchline, Scotland
    Posts
    1,092
    Thanks
    15
    Thanked 1 Time in 1 Post

    Exclamation apostrophe crashes my MySQL database

    Code:
    set rscountrycode = con.execute("select CountryCode from dbo_tp_countryname where CountryName like '%" & country & "%'")
    the variable country sometimes has a apostrophe ( ' ) in it, however, when it does it knocks out my MySQL statement and crashes my DB.

    Although, i need to have this apostophe in my statement as when i am searching for CountryName of 'Korea (Democratic People's Republic Of)' then it will not return any records due to the apostrophe.

    Other than removing all apostrophies from the DB, which i have posted another related question to in the MySQL forum, how can I get around this????

    TIA.

    Picco

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    There is a thread on this at the top of the forum.
    Single Quotes give me a syntax error!

  • #3
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    set rscountrycode = con.execute("select CountryCode from
    dbo_tp_countryname where CountryName like '%" & Replace(country, "'", "''") & "%'")
    Fix is in red. Use Replace to subsititue one apostrophe for two apostrophes. In case that is hard to read after country, it's double quote, single quote, double quote, comma, double quote, single quote, single quote and then double quote.

  • #4
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    The replace will work if you only have one or two instances, otherwise it is far easier to do what is illustrated in the sticky that Spudhead already linked to and create a function to do it.
    OracleGuy


  •  

    Posting Permissions

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