postmodern-parametized-statements

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)

("Denmark")

(defprepared test22

  (:select 'name

           :from 'countries

           :where (:= 'id '$1)))

(test22 5)

("Denmark")

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 '(21 6 5)) 

("EU" "Denmark" "US")

In older versions of Postmodern you would have needed to use a vector rather than a list.

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

You cannot use a list or vector 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,  use a list or vector if you use the keyword any. E.g.

(query "select name from countries where id = any($1)"

        '(21 20))

(("UK") ("US"))

Now the s-sql version. Note the change for any to any*. Again, in older versions of Postmodern you would have had to use a vector rather than a list.

(query (:select 'name

                :from 'countries

                :where (:= 'id (:any* '$1)))

       '(21 20))

(("UK") ("US"))

Postmodern-sec-9