OCaml Forge
Tracker
SCM

Detail: [#955] Quotes in strings passed to postgresql

Bugs: Browse | Download .csv | Monitor

[#955] Quotes in strings passed to postgresql

Date:
2011-04-19 13:10		 Priority:
3
State:
Open
Submitted by:
Philippe Veber (pveber) 		Assigned to:
Nobody (None)
Hardware:
All		 Product:
None
Operating System:
All		 Component:
None
Version:
v1.1		 Severity:
major
Resolution:
None		  
URL:
Summary:
Quotes in strings passed to postgresql

Detailed description
Simple quotes in strings passed to the posgresql server should be properly escaped. Otherwise this leads to incorrect queries. Here is a simple example :

# let t = <:table<t (id text) >>;;
val t :
(< id : < get : unit; nul : Sql.nullable; t : Sql.string_t > Sql.t >,
< > Sql.writable)
Sql.view = <abstr>
# Sql.sql_of_query (<:insert< $t$ := { id = "a'a" } | >>)
;;
- : string = "INSERT INTO t ((SELECT 'a'a' AS id))"

The solution is given in the manual, see http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html, section 4.2.1.2

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g. 'Dianne''s horse'. Note that this is not the same as a double-quote character (").


Followup

Message
Date: 2011-04-19 15:51
Sender: Philippe Veber

It seems that this kind of encoding is not postgres-specific, so I propose the following patch

diff -rN old-macaque/src/sql_printers.ml new-macaque/src/sql_printers.ml
27a28,53
> (* adapted from standard library String module
> it escapes simple quotes and then calls String.escaped *)
> let string_sql_escaped s =
> let n = ref 0 in
> for i = 0 to String.length s - 1 do
> n := !n + (if String.unsafe_get s i = '\'' then 2 else 1)
> done;
> let r =
> if !n = String.length s then s else (
> let s' = String.create !n in
> n := 0;
> for i = 0 to String.length s - 1 do
> let c = String.unsafe_get s i in
> if c = '\'' then (
> String.unsafe_set s' !n '\'';
> incr n
> ) ;
> String.unsafe_set s' !n c ;
> incr n
> done;
> s'
> )
> in String.escaped r
>
>
>
144c170
< | String s -> quote String.escaped s
---
> | String s -> quote string_sql_escaped s

Attached Files:

Changes:

No Changes Have Been Made to This Item