escaping quotes

[plork123]

hi all

i'minserting data into a table that has single quotes, this errors in postgresql

can anyone tell me how i escape the single quotes from within postgresql

e.g

insert into test values (123, 9876.5432, "Isn't this fun!")
fails

 

[feherke]

Hi

insert into test values (123, 9876.5432, 'Isn[red]'[/red]'t this fun!');

[gray]-- or[/gray]

insert into test values (123, 9876.5432, 'Isn[red]\[/red]'t this fun!');

Double quotes are for identifiers, not for values.

Feherke.

http://rootshell.be/~feherke/

 

[plork123]

thanks for the reply but i won't know what the 3rd value will be, that's being passed through

thanks

 

[feherke]

Hi

Passed through what ? In which language is written your application ? In some languages already exists quoting functions. But the correct way ( although I dislike it ) is to use parameters.

Feherke.

http://rootshell.be/~feherke/

 

[plork123]

i have a postgres function used by applications - but cannot rely on those applications to escape the parameters

So i want to do it at the postgres end as a double check to make sure it doesn't fall over

 

[feherke]

Hi

cannot rely on those applications to escape the parameters

Then make backups often. Only who does not want will not corrupt your data.

Beside this, post your function. I did not worked to much with functions, but if I see the code, maybe I will have an idea. Or someone else will have.

Feherke.

http://rootshell.be/~feherke/

 

[ceco]

if it is postgresql function you have to escape its parameters too!!

you call the functions something like that

SELECT my_function('this is function''s parameter');

and you have to quote the parameters!!

the only other way beside quoting is using bind variables (BUT it is still client side)

 

[gpicazo]

Try using

PGSQL_ESCAPE_STRING