Insert text with single quotes in PostgreSQL

Question

I have a table test id name    I need to insert values like   user s log   my user   customer s    insert into test values  1  user s log     insert into test values  2   my users      insert into test values  3  customer s      I am getting an error if I run any of the above statements   If there is any method to do this correctly please share  I don t want any prepared statements   Is it possible using sql escaping mechanism

User · Answer

In postgresql if you want to insert values with   in it then for this you have to give extra     insert into test values  1  user  s log     insert into test values  2    my users       insert into test values  3  customer  s

User · Answer

This is so many worlds of bad  because your question implies that you probably have gaping SQL injection holes in your application   You should be using parameterized statements  For Java  use PreparedStatement with placeholders   You say you don t want to use parameterised statements  but you don t explain why  and frankly it has to be a very good reason not to use them because they re the simplest  safest way to fix the problem you are trying to solve   See Preventing SQL Injection in Java  Don t be Bobby s next victim   There is no public function in PgJDBC for string quoting and escaping  That s partly because it might make it seem like a good idea   There are built-in quoting functions quote literal and quote ident in PostgreSQL  but they are for PL PgSQL functions that use EXECUTE  These days quote literal is mostly obsoleted by EXECUTE     USING  which is the parameterised version  because it s safer and easier  You cannot use them for the purpose you explain here  because they re server-side functions     Imagine what happens if you get the value    DROP SCHEMA public -- from a malicious user  You d produce   insert into test values  1     DROP SCHEMA public --      which breaks down to two statements and a comment that gets ignored   insert into test values  1      DROP SCHEMA public  --      Whoops  there goes your database

User · Answer

select concat       abc

User · Answer

According to PostgreSQL documentation  4 1 2 1  String Constants     To include a single-quote character within a string constant  write two   adjacent single quotes  e g   Dianne  s horse     See also the standard conforming strings parameter  which controls whether escaping with backslashes works

User · Answer

If you need to get the work done inside Pg   to json value   https   www postgresql org docs 9 3 static functions-json html FUNCTIONS-JSON-TABLE

User · Answer

you can use the postrgesql chr int  function   insert into test values  2     chr 39    my users   chr 39

User · Answer

String literals Escaping single quotes   by doubling them up - gt     is the standard way and works of course   user s log      -- incorrect syntax  unbalanced quote   user  s log  In old versions or if you still run with standard conforming strings   off or  generally  if you prepend your string with E to declare Posix escape string syntax  you can also escape with the backslash    E user  s log   Backslash itself is escaped with another backslash  But that s generally not preferable  If you have to deal with many single quotes or multiple layers of escaping  you can avoid quoting hell in PostgreSQL with dollar-quoted strings   escape    with         escape   with      To further avoid confusion among dollar-quotes  add a unique token to each pair   token escape   with    token   Which can be nested any number of levels   token2 Inner string   token1 escape   with    token1  is nested token2   Pay attention if the   character should have special meaning in your client software  You may have to escape it in addition  This is not the case with standard PostgreSQL clients like psql or pgAdmin  That is all very useful for writing plpgsql functions or ad-hoc SQL commands  It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible  though   Craig s answer has more on that  More details   SQL injection in Postgres functions vs prepared queries  Values inside Postgres When dealing with values inside the database  there are a couple of useful functions to quote strings properly   quote literal   or quote nullable   - the latter outputs the string NULL for null input   There is also quote ident   to double-quote strings where needed to get valid SQL identifiers   format   with the format specifier  L is equivalent to quote nullable    Like  format   L   string var  concat   or concat ws   are typically no good for this purpose as those do not escape nested single quotes and backslashes

[postgresql] Insert text with single quotes in PostgreSQL

Examples related to postgresql

Examples related to insert

Examples related to special-characters

Examples related to quotes