Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySQL - Escape HTML Characters

 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • 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 ]
     
    Peer Reynders
    Bartender
    Posts: 2968
    6
    • Mark post as helpful
    • send pies
    • 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
    Saloon Keeper
    Posts: 4033
    18
    Eclipse IDE Flex Google Web Toolkit
    • Mark post as helpful
    • send pies
    • 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
    • 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
    Saloon Keeper
    Posts: 4033
    18
    Eclipse IDE Flex Google Web Toolkit
    • Mark post as helpful
    • send pies
    • 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
    • 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.
     
    Jeanne Boyarsky
    author & internet detective
    Marshal
    Posts: 35279
    384
    Eclipse IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • 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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic