• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySQL - Escape HTML Characters

 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I need to escape characters for a MySQL insert, but it has to be done in SQL instead of Java since its part of a trigger. In particular:

  • & --> &
  • < --> &lt;
  • > --> &gt;
  • ' --> &apos;
  • \ --> &quote;

  • Right now, the following works:

    But this is awfully messy... any chance there's something more elegant that can be done in one line?
    [ February 12, 2008: Message edited by: Scott Selikoff ]
     
    Bartender
    Posts: 2968
    6
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by Scott Selikoff:But this is awfully messy... any chance there's something more elegant that can be done in one line?



    I guess this excludes putting the EncodeHtml/DecodeHtml functionality into a pair of MySQL stored functions that you can use in your SELECT?
     
    Scott Selikoff
    author
    Posts: 4335
    39
    jQuery Eclipse IDE Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I could, but the function would perform the same as the code above. Since I'm using code generation, it doesn't make much difference whether I use a procedure call or not, just what runs the best.
     
    Peer Reynders
    Bartender
    Posts: 2968
    6
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by Scott Selikoff:
    it doesn't make much difference whether I use a procedure call or not, just what runs the best.



    I don't know about MySQL but usually stored functions are already compiled and therefore perform better than the equivalent dynamic code. And regardless whether its generated, I think that a stored function version would be more readable and observes the DRY principle better. Furthermore it allows you to update your encoding/decoding strategy with a mere update in the database while leaving the generating code untouched.
     
    Scott Selikoff
    author
    Posts: 4335
    39
    jQuery Eclipse IDE Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks Peer, you're probably right about precompiling stored procedures, so should generate a function as part of the database install. As for readibility/management.... most triggers (especially these) are write once read never type stuff... you could never just modify these sucker
     
    Peer Reynders
    Bartender
    Posts: 2968
    6
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Originally posted by Scott Selikoff:
    As for readibility/management.... most triggers (especially these) are write once read never type stuff... you could never just modify these sucker



    Well, triggers are different beasts from stored procedures and functions and aren't necessarily pre-compiled either. However I'm still inclined to move whatever functionality I can from a trigger into a stored procedure or function whenever I can.
     
    author & internet detective
    Posts: 41860
    908
    Eclipse IDE VI Editor Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I don't inherently favor or be against stored procedures/functions. In this case, the SQL is already in the database layer (a trigger.) So it makes sense to me to go all the way in and put it in a stored function.
    reply
      Bookmark Topic Watch Topic
    • New Topic