[postgresql] Insert text with single quotes in PostgreSQL

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?

This question is related to postgresql insert special-characters quotes

The answer is


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.


select concat('''','abc','''')

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


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.


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');

you can use the postrgesql chr(int) function:

insert into test values (2,'|| chr(39)||'my users'||chr(39)||');

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to insert

How to insert current datetime in postgresql insert query How to add element in Python to the end of list using list.insert? Python pandas insert list into a cell Field 'id' doesn't have a default value? Insert a row to pandas dataframe Insert at first position of a list in Python How can INSERT INTO a table 300 times within a loop in SQL? How to refresh or show immediately in datagridview after inserting? Insert node at a certain position in a linked list C++ select from one table, insert into another table oracle sql query

Examples related to special-characters

How to use tick / checkmark symbol (?) instead of bullets in unordered list? HTML for the Pause symbol in audio and video control How to run mysql command on bash? Which characters need to be escaped when using Bash? Matching special characters and letters in regex jQuery: Check if special characters exists in string Checking if a character is a special character in Java How to display special characters in PHP How should I escape commas and speech marks in CSV files so they work in Excel? grep for special characters in Unix

Examples related to quotes

YAML: Do I need quotes for strings in YAML? Regex to split a CSV Expansion of variables inside single quotes in a command in Bash How to call execl() in C with the proper arguments? Insert text with single quotes in PostgreSQL When to use single quotes, double quotes, and backticks in MySQL Difference between single and double quotes in Bash Remove quotes from a character vector in R How do I remove quotes from a string? How can I escape a double quote inside double quotes?