[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SQL injection with guile-pg

From: Greg Troxel
Subject: SQL injection with guile-pg
Date: Thu, 06 Jan 2005 11:52:29 -0500

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

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)
      (((ssp-manager) 'select)
        (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)

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

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

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.

reply via email to

[Prev in Thread] Current Thread [Next in Thread]