Thread: function to escape single quotes
Hi,
I would like to write a PL/PGSQL function that would escape any occurence of single quotes in a string, and call this function before inserting the unescaped strings into some tables. Is this a good approach to make sure all strings are escaped before inserting into tables? I thought this would be a good idea as there would be a single point of control on escaping the strings.
For example, in our customer_data table we have the fields firstname,lastname, street etc. Some people's name have single quotes in them, and some people have addresses with single quotes in them. So I need to write a function that would take any string, see if it has a quote in it and escape it. Then, is it possible to write a trigger before inserting to call this function and then perform the insert??
Please give me some pointers on writing this function and creating the trigger.
Thanks,
Saranya
Start your day with Yahoo! - make it your home page
Saranya Sivakumar <sarlavk@yahoo.com> writes:
> I would like to write a PL/PGSQL function that would escape any
> occurence of single quotes in a string, and call this function before
> inserting the unescaped strings into some tables. Is this a good
> approach to make sure all strings are escaped before inserting into
> tables?
By the time the data gets to a pl/pgsql function, the damage (if any)
would already have been done. You pretty much have to deal with this
issue on the client application side.
BTW there already are such functions available, you need not write your
own --- see quote_literal(). About the only context in which this is
needed in plpgsql is constructing dynamic query strings for use in
EXECUTE.
regards, tom lane