SQL injection with guile-pg

[Greg Troxel]

I'm writing some code with guile-pg, and on reading:

  http://www.unixwiz.net/techtips/sql-injection.html

went to check my code.

My application sends multicast packets with key/value pairs using the
print representation of alists, basically.  On receipt of a packet from
host "foo", the strings are updated in a table with columns hostname,
key and value.
I am using the single-table abstraction for most of this, and find
that it quotes properly (using sql-quote in postgres-tables.scm) when
using type converters.

However, in order to delete the old value, and to look up values, I
used a where clause that I had constructed.  This turned out to be
vulnerable since it did not quote.

My code looked something like this

;; Make where clause for hostname and key
(define (ssp-where-body hostname key)
  (string-append "hostname = '" hostname
                  "' and key = '" key "'"))

(define (ssp-where hostname key)
  (where-clausifier (ssp-where-body hostname key)))

;; Fetch value for hostname and key.  Return #f if not in database.
;; Return 'multiple if there are multiple values (this is an error,
;; but ssp-delete still should delete them in this case.
(define-public (ssp-fetch hostname key)
  (let
      ((r
      (((ssp-manager) 'select)
       "value"
        (ssp-where hostname key))))
    (if (and (equal? 'PGRES_TUPLES_OK (pg-result-status r))
         (> (pg-ntuples r) 0))
         (if (= 1 (pg-ntuples r))
             (pg-getvalue r 0 0)
                 'multiple)
                 #f)))

With that, the following dropped my table:

(ssp-fetch "foobar" "test'; drop table ssp; select * from configuredlocation 
where hostname = 'foo")

So, I changed to


With that, inserting unreasonable strings worked fine:

guile> (ssp-update "foobar" "test'; drop table ssp; select * from 
configuredlocation where hostname = 'foo" "bar")
#<PG-RESULT:60:PGRES_COMMAND_OK:0:0>


psql# select * from ssp;
 hostname |                                      key                            
          | value 
----------+-------------------------------------------------------------------------------+-------
 foobar   | test'; drop table ssp; select * from configuredlocation where 
hostname = 'foo | bar
(1 row)

guile> (ssp-fetch "foobar" "test'; drop table ssp; select * from 
configuredlocation where hostname = 'foo")
"bar"


I don't mean to criticize guile-pg; the error above was mine in using
input data unquoted in a query, and is a standard SQL newbie error.

It would be nice, though, to have sql-quote as a user-accessible procedure.

It would be further cool to do two things:

  use bound parameters, so that the strings aren't part of the sql
  command, but are passed as data


  have some support to make a sql command fragment with safe/quoted type 
conversion, perhaps something like
  (sql-prep "select foo from bar where a = " (list 'text s) ";")
  where s is a string.

Thanks to ttn for maintaining guile-pg; despite my wishes for more I'm
glad to be doing things in guile rather than perl.