Consider a perl function like this
CREATE OR REPLACE FUNCTION public.test(text) RETURNS VOID AS
$body$
my $var = shift;
spi_exec_query("INSERT INTO tbltest(field1) VALUES ('$var')");
$body$
LANGUAGE 'plperlu' VOLATILE;
and a table
CREATE TABLE tbltest (
field1 text
) WITHOUT OIDS;
When I call the function like
SELECT test('Do this')
it works. But when I call it like
SELECT test('Don't do that')
it fails (of course). So I call it like
SELECT test('Don''t do that')
It still fails and I do understand why. But how do I solve it in the function?
Using a prepared statement would be a solution, like
$sel = spi_prepare('SELECT test($1)', 'text');
spi_exec_prepared($sel, $var);
But I need to get it solved without prepared statements.
What is the best approach considering that $var could contain both
single and double quotes?
Thanks