Postmodern Examples Table of Contents Postmodern-sec-7-2

Parametized Statements

Parametized statements help protect against sql injection and some of the examples above have used parameterized statement forms. You can't parameterize table names, column names or sql keywords. So if you are getting those from the user, you definitely need to sanitize the input. Parameterized statements also don't protect against other things like cross-script attacks, so you still need to sanitize input.

The following is a simple parameterized query and a prepared statement using parameters. First, the pure sql version

(query "select name from countries where name=$1" "France" :single)

Now the s-sql version:

(query (:select 'id :from 'countries :where (:= 'name '$1)) "France" :single)

Now the simple prepared statement version in standard sql and s-sql:

(defprepared test21 "select name from countries where id=$1")

(test21 5)

(defprepared test22
  (:select 'name
           :from 'countries
           :where (:= 'id '$1)))
(test22 5)

Now let's change the simple version to one where you want to give it a list.

(defprepared test23 "select name from countries where id=any($1)" :column) (test23 '(3 21 17)) ; Evaluation aborted on #<SIMPLE-ERROR "Value ~S can not be converted to an SQL literal." {10033E73A1}>.

What happened? You can't use a list here. You can, however, use a vector:

(test23 (vector 21 6 5))

("EU" "Denmark" "US")

Moral of the story: you will have to coerce the list to a vector:

(test23 (coerce '(21 6 5) 'vector)) ("EU" "Denmark" "US")

You also cannot use a list with the sql keyword "in". E.g.

(query "select name from countries where id in $1" '(21 20)) Evaluation aborted on #<CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {100C262F31}>.

You can, however, convert it to a vector and use the keyword any. E.g.

(query "select name from countries where id = any($1)" (coerce '(21 20) 'vector)) (("UK") ("US"))

Now the s-sql version. Note the change for any to any*

(query (:select 'name :from 'countries :where (:= 'id (:any* '$1))) (coerce '(21 20) 'vector)) (("UK") ("US"))