S-SQL Examples P

Parameterized Statements

Parameterized 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")

Now the s-sql version

(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. We are going to use the :column parameter to indicate we just want a single list of all the country names found with the select statement.

(defprepared test23 "select name from countries where id=any($1)"
  :column)

(test23 '(21 6 5))

("EU" "Denmark" "US")

You also get the same result if you pass a vector instead of 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 a vector with the keyword any. E.g.

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

("UK" "US")

(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*

  (query (:select 'name
                  :from 'countries
                  :where (:= 'id (:any* '$1)))
         '(21 20) :column)

  ("UK" "US")

  (query (:select 'name
                :from 'countries
                :where (:= 'id (:any* '$1)))
       (vector 21 20) :column)

("UK" "US")

Partition-by

Partition-by is not table partitioning. Rather it is a clause that allows you to set the range of records that will be used for each group within an over clause. Consider it a windowing function. Partition-by is available in Postmodern as of the Oct 29, 2013 git version.

Important: Note use of :order-by without being the function call at the beginning of a form.

(query (:select 'depname 'empno 'salary
                (:over (:avg 'salary)
                       (:partition-by 'depname))
                :from 'empsalary))

(query (:select 'depname 'empno 'salary
                (:over (:rank)
                       (:partition-by 'depname :order-by (:desc 'salary)))
                :from 'empsalary))

S-SQL Examples Home Page

Parameterized Statements

Partition-by