Insertar texto con comillas simples en PostgreSQL
Tengo una mesa test(id,name).
Necesito insertar valores como: 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');
Recibo un error si ejecuto cualquiera de las declaraciones anteriores.
Si existe algún método para hacer esto correctamente, compártalo. No quiero declaraciones preparadas.
¿Es posible utilizar el mecanismo de escape SQL?
Literales de cadena
Escapar de comillas simples 'duplicándolas → ''es la forma estándar y, por supuesto, funciona:
'user's log' -- incorrect syntax (unbalanced quote)
'user''s log'Comillas simples (código ASCII/UTF-8 39), eso sí, no comillas invertidas `, que no tienen ningún propósito especial en Postgres (a diferencia de otros RDBMS) y no comillas dobles ", utilizadas para identificadores.
En versiones anteriores o si todavía ejecuta standard_conforming_strings = offo, en general, si antepone su cadena con Epara declarar la sintaxis de cadena de escape Posix , también puede escapar con la barra invertida \:
E'user\'s log'
La propia barra invertida se escapa con otra barra invertida. Pero eso generalmente no es preferible.
Si tiene que lidiar con muchas comillas simples o múltiples capas de escape, puede evitar las comillas en PostgreSQL con cadenas entre comillas en dólares :
'escape '' with '''''
$$escape ' with ''$$
Para evitar aún más confusión entre las cotizaciones del dólar, agregue un token único a cada par:
$token$escape ' with ''$token$
Que se puede anidar cualquier número de niveles:
$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$
Preste atención si el $carácter debe tener un significado especial en el software de su cliente. Es posible que tengas que escapar de él además. Este no es el caso de los clientes PostgreSQL estándar como psql o pgAdmin.
Todo esto es muy útil para escribir funciones PL/pgSQL o comandos SQL ad-hoc. Sin embargo, no puede aliviar la necesidad de utilizar declaraciones preparadas o algún otro método para protegerse contra la inyección de SQL en su aplicación cuando la entrada del usuario es posible. La respuesta de @ Craig tiene más información sobre eso. Más detalles:
- Inyección SQL en funciones de Postgres vs consultas preparadas
Valores dentro de Postgres
Cuando se trata de valores dentro de la base de datos, existen un par de funciones útiles para citar cadenas correctamente:
quote_literal()oquote_nullable()- este último genera la cadena sin comillasNULLpara una entrada nula.
También es posiblequote_ident()incluir cadenas entre comillas dobles cuando sea necesario para obtener identificadores SQL válidos .format()con el especificador de formato%Lequivale aquote_nullable().
Como:format('%L', string_var)concat()concat_ws()
Según la documentación de PostgreSQL (4.1.2.1. Constantes de cadena) :
Para incluir un carácter de comilla simple dentro de una constante de cadena, escriba dos comillas simples adyacentes, por ejemplo, 'El caballo de Dianne'.
Consulte también el standard_conforming_stringsparámetro, que controla si funciona el escape con barras invertidas.
Esto es muchísimo malo, porque su pregunta implica que probablemente tenga grandes agujeros de inyección SQL en su aplicación.
Deberías utilizar declaraciones parametrizadas. Para Java, utilícelo PreparedStatementcon marcadores de posición . Dice que no desea utilizar declaraciones parametrizadas, pero no explica por qué y, francamente, tiene que ser una muy buena razón para no usarlas porque son la forma más sencilla y segura de solucionar el problema que está intentando. resolver.
Consulte Prevención de la inyección de SQL en Java . No seas la próxima víctima de Bobby .
No existe ninguna función pública en PgJDBC para citar cadenas y escapar. Esto se debe en parte a que podría parecer una buena idea.
Hay funciones de cotización integradas quote_literalen quote_identPostgreSQL, pero son para PL/PgSQLfunciones que usan EXECUTE. Hoy en día quote_literalestá prácticamente obsoleto EXECUTE ... USING, que es la versión parametrizada , porque es más segura y sencilla . No puedes usarlos para el propósito que explicas aquí, porque son funciones del lado del servidor.
Imagínese lo que sucede si obtiene el valor ');DROP SCHEMA public;--de un usuario malintencionado. Producirías:
insert into test values (1,'');DROP SCHEMA public;--');
que se divide en dos declaraciones y un comentario que se ignora:
insert into test values (1,'');
DROP SCHEMA public;
--');
Ups, ahí va tu base de datos.
En postgresql, si desea insertar valores en 'él, para ello debe dar más'
insert into test values (1,'user''s log');
insert into test values (2,'''my users''');
insert into test values (3,'customer''s');
