- postmodern-devel - mailman3.common-lisp.net

[postmodern-devel] sql injection
by Phil Marneweck 25 Jun '10

25 Jun '10

Hi How susceptible is dao objects to sql injection and what measures would be suggested to prevent sql injection if it is possible with dao objects. Thank You

2 2

[postmodern-devel] Local connection to Postgresql under FreeBSD
by rrl 18 May '10

18 May '10

Hi, I can connect to Postgresql using a Unix socket with sbcl by passing :unix as a host. The problem is that under FreeBSD, the default directory for the socket is ``/tmp'' and not ``/var/run/postgresql''. Here's a quick fix that solves the problem: --- public.lisp.orig 2010-04-14 21:41:39.000000000 +0300 +++ public.lisp 2010-05-15 11:21:56.000000000 +0300 @@ -47,7 +47,9 @@ #+(and sbcl unix) (defun sb-unix-socket-connect (port) (let ((sock (make-instance 'sb-bsd-sockets:local-socket :type :stream)) - (addr (format nil "/var/run/postgresql/.s.PGSQL.~a" port))) + (addr (format nil "~a/.s.PGSQL.~a" + #-freebsd "/var/run/postgresql" #+freebsd "/tmp" + port))) (sb-bsd-sockets:socket-connect sock addr) (sb-bsd-sockets:socket-make-stream sock :input t :output t :element-type '(unsigned-byte 8)))) Maybe a better fix would be to define a variable for the socket directory and let the user set it himself.

2 1

[postmodern-devel] Compile error: about to bind S-SQL::CH to NIL, which is not of type CHARACTER.
by Frank Goenninger 16 May '10

16 May '10

Hi there, being totally new to postmodern I have encountered the following problem: (Environment: AllegroCL Express Edition on Mac OS X, newest postmodern bits from github) ;;; Compiling file ;;; /opt/common-lisp/packages/postmodern/postmodern/util.lisp ; While compiling LIST-TABLES: about to bind S-SQL::CH to NIL, which is not of type CHARACTER. [Condition of type TYPE-ERROR] ... not digging into it yet, I wonder if anybody has seen this, too ... Thanks!! Regards Frank

2 3

[postmodern-devel] Passing a list to a prepared query
by Sebastian Tennant 16 May '10

16 May '10

Hi, I'd like to do this: (defprepared addresses-by-ids "SELECT address FROM users WHERE id IN $1" :column) and then call it like so: CLUSER> (addresses-by-ids "(1,2,3)") Is this possible? I've tried a few different approaches but keep getting errors. Many thanks for great software by the way. Seb -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap

2 2

[postmodern-devel] cl-postgres/sbcl crashes with bogus stack frame
by Andrew Golding 07 May '10

07 May '10

Kind of a general heads-up. I have actually been using CLSQL and saw the bit in one of the older messages about getting a similar result in CLSQL. Anyways, if the foreign code messes with signal handling, even signals SBCL isn't currently using, it has the potential to crash the program. I'll try to have a look at postmodern this weekend and see if I can reproduce and solve the problem.

1 0

[postmodern-devel] cl-postgres/sbcl crashes with bogus stack frame
by david thompson 06 May '10

06 May '10

Any ideas what might trigger cl-postgres (postmodern 1.14) coming to its knees with the backtrace shown below? No error or condition description is given - sbcl (1.0.28) just retires from active duty and drops into the debugger with the ("bogus stack frame") indication. The postgresql server (8.3) log doesn't have any indication of trouble at the time cl-postgres seems to run into trouble. This seems to only happen intermittently (every few thousand queries), irreproducibly (doesn't seem to be associated with a specific query or anything along those lines), and only when the postgresql server is being utilized by several lisp instances concurrently. Thanks in advance for any input/ideas/troubleshooting suggestions... - Alan ... 32: ("bogus stack frame") 33: (SB-IMPL::SUB-SUB-SERVE-EVENT NIL NIL) 34: (SB-IMPL::SUB-SERVE-EVENT NIL NIL NIL) 35: (SB-SYS:WAIT-UNTIL-FD-USABLE 5 :INPUT NIL) 36: (SB-IMPL::REFILL-INPUT-BUFFER #<SB-SYS:FD-STREAM for "a socket" {C265BA9}>) 37: (SB-IMPL::INPUT-UNSIGNED-8BIT-BYTE #<SB-SYS:FD-STREAM for "a socket" {C265BA9}> T NIL) 38: (CL-POSTGRES::SKIP-BYTES #<unavailable argument> #<unavailable argument>) 39: (CL-POSTGRES::TRY-TO-SYNC #<SB-SYS:FD-STREAM for "a socket" {C265BA9}> T) 40: ((FLET #:CLEANUP-FUN-[FORM-FUN-[SEND-QUERY]389]390))[:CLEANUP] 41: (CL-POSTGRES::SEND-QUERY #<unavailable argument> #<unavailable argument> #<unavailable argument>) 42: ((LABELS #:G161)) 43: (SQLG::QUERY-CORE "select program from programs where programskey = '192828' ;" 20 #<unused argument>)

4 5

[postmodern-devel] NIL interpreted as Boolean
by Patrick May 06 May '10

06 May '10

I have a class that looks something like this: (defclass call-request () ((phone-number :initarg :phone-number :accessor phone-number :type string :col-type string) (call-time :initarg :callback-time :accessor callback-time :type (or null simple-date:timestamp :col-type (or db-null numeric) :initform nil) (extension :initarg :extension :accessor extension :type (or string null) :col-type (or db-null string) :initform nil)) (:metaclass postmodern:dao-class) (:table-name "call_request") (:keys phone-number) (:documentation "A request to be called.")) When I try to insert a call-request with no call-time or extension, I get the error: Database error 42804: column "call_time" is of type time with time zone but expression is of type boolean You will need to rewrite or cast the expression. Query: INSERT INTO call_request (extension, callback_time, phone_number) VALUES (false, false, E'8005551212') [Condition of type CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION] How do I make Postmodern interpret nil as db-null rather than boolean? Thanks, Patrick

3 6

[postmodern-devel] Examples of using CLOS interface
by Patrick May 15 Apr '10

15 Apr '10

I've recently found Postmodern and it looks like a nice, lighter weight alternative to CL-SQL. Is there any detailed documentation for using Postmodern with CLOS? How about a richer example than that in the Database access objects section of the manual? Thanks, Patrick

1 0

[postmodern-devel] PATCH: New query formats - plists and plist
by Andrey Moskvitin 14 Apr '10

14 Apr '10

Hi, I propose to add new query formats: diff --git a/doc/postmodern.html b/doc/postmodern.html index 3691ae8..9ac466f 100644 --- a/doc/postmodern.html +++ b/doc/postmodern.html @@ -202,6 +202,10 @@ row.</td></tr> <tr><td><code>:list</code>, <code>:row</code></td><td>Return a single row as a list.</td></tr> + <tr><td><code>:plists</code></td><td>Return a list of plists which map column + names to values,with the names represented as + keywords.</td></tr> + <tr><td><code>:plist</code></td><td>Return a single row as an plist.</td></tr> <tr><td><code>:alists</code></td><td>Return a list of alists which map column names to values,with the names represented as keywords.</td></tr> diff --git a/postmodern/query.lisp b/postmodern/query.lisp index 3e5bfa5..921d03d 100644 --- a/postmodern/query.lisp +++ b/postmodern/query.lisp @@ -9,6 +9,15 @@ :for symbol :in symbols :collect (cons symbol (next-field field)))))) +;; Like symbol-alist-row-reader, but return plist +(def-row-reader symbol-plist-row-reader (fields) + (let ((symbols (map 'list (lambda (desc) (from-sql-name (field-name desc))) fields))) + (loop :while (next-row) + :collect (loop :for field :across fields + :for symbol :in symbols + :collect symbol + :collect (next-field field))))) + ;; A row-reader for reading only a single column, and returning a list ;; of single values. (def-row-reader column-row-reader (fields) @@ -22,6 +31,8 @@ (:list list-row-reader t) (:rows list-row-reader nil) (:row list-row-reader t) + (:plists symbol-plist-row-reader nil) + (:plist symbol-plist-row-reader t) (:alists symbol-alist-row-reader nil) (:alist symbol-alist-row-reader t) (:str-alists alist-row-reader nil) Moskvitin Andrey

3 6

[postmodern-devel] PATCH: Add support for asynchronous notifications
by Zach Beane 08 Apr '10

08 Apr '10

Attached is a patch that adds Postmodern support for asynchronous notifications, as documented here: http://www.postgresql.org/docs/current/static/sql-listen.html http://www.postgresql.org/docs/current/static/sql-notify.html http://www.postgresql.org/docs/current/static/sql-unlisten.html and http://www.postgresql.org/docs/current/interactive/protocol-message-formats… (under "NotificationResponse") It updates MESSAGE-CASE to watch for async notifications and, by default, issue a warning that looks very similar to what the psql client displays for async notifications: WARNING: Asynchornous notification "lisp" received from server process with PID 22063. It also adds a blocking function, WAIT-FOR-NOTIFICATION, that adds a handler-bind around the warning and returns the notification data when a notification is received. The patch isn't complete, though -- I wasn't sure how best to integrate the new public function, condition, and condition accessors into the documentation. How should I proceed? Zach

2 1

[postmodern-devel] Sorry about the git repository confusion!
by Marijn Haverbeke 08 Apr '10

08 Apr '10

Initially, I published a 'git clone' example that didn't even work. Then, it worked, but was dog-slow because I hadn't enabled git's HTTP cgi script. Now finally, things are ducky, but the URL changed again: git clone http://marijn.haverbeke.nl/git/postmodern GitHub also continues to work, of course. Cheers, Marijn

1 0

[postmodern-devel] [patch] Erratic behavior when NIL password is given
by J.P. Larocque 07 Apr '10

07 Apr '10

Hi, I usually use host-based authentication with ident to connect to PostgreSQL from trusted machines, so I've been specifying a password of NIL for a long time. However, when passwordless authentication fails and NIL is given as a password, POSTMODERN:CONNECT will behave erratically. In my case, the symptoms are: * SBCL (Debian 1:1.0.18.0-2) RAM usage balloons to hundreds of megabytes. * I get this condition printed, along with a tag-along (obscure and what I assume to be) object, for the heck of it: ---8<---8<--- * (postmodern:connect "piranha" "piranha" nil "localhost") debugger invoked on a TYPE-ERROR in thread #<THREAD "initial thread" RUNNING {1002669C31}>: The value NIL is not of type (MOD 1152921504606846975). Type HELP for debugger help, or (SB-EXT:QUIT) to exit from SBCL. restarts (invokable by number or by possibly-abbreviated name): 0: [ABORT] Exit debugger, returning to top level. (SB-KERNEL:UB32-BASH-COPY "piranha" 0 "" NIL 7)[:EXTERNAL] --->8--->8--- That's invoking SBCL on the command-line. When I use SLIME: * SBCL, like a gas, expands to fill all available space (e.g. ~1.8GiB before I ran out of RAM+swap and rebooted the virtual machine). * During this time, nothing ever comes back to emacs. * If I do a SLIME interrupt before the system completely dies, emacs takes a while to do anything, then enters the emacs (not SLIME!) debugger, and informs me that the "variable binding depth exceeds max-specpdl-size" at every available opportunity until I restart emacs. It turns out bad things happen when you optimize for safety 0. Making this change: diff -x'*~' -urN postmodern-1.16/cl-postgres/package.lisp postmodern-1.16-no_opt/cl-postgres/package.lisp --- postmodern-1.16/cl-postgres/package.lisp 2010-04-02 04:23:24.000000000 -0700 +++ postmodern-1.16-no_opt/cl-postgres/package.lisp 2010-04-06 06:00:47.000000000 -0700 @@ -83,5 +83,5 @@ (eval-when (:compile-toplevel :load-toplevel :execute) ;; Optimization settings (only used by functions that need it). (defparameter *optimize* - '(optimize (speed 3) (safety 0) (space 1) (debug 1) + '(optimize (speed 3) (space 1) (debug 1) (compilation-speed 0)))) yields a much more sensible, instantaneous, and non-ballooning: The value NIL is not of type STRING. [Condition of type TYPE-ERROR] Of course, bad things also happen when you pass objects of the wrong type to a function. (It just never sat well with me to pass a string password when no password was needed; NIL seemed to make much more sense, and it worked...) I've attached a patch to: * Check the types of arguments given to CL-POSTGRES:OPEN-DATABASE. * Update the HTML documentation to describe the expected types of arguments to CL-POSTGRES:OPEN-DATABASE and POSTMODERN:CONNECT. * Update the documentation strings for the above two functions to contain the same content as their descriptions in the HTML documentation. I also humbly suggest not optimizing for safety 0. One of the big reasons I use CL, and not something like C, is because I value safety: when one thing breaks, that breakage is self-contained and can be tracked down due to conditions. I don't want my Lisp process to go down like the Hindenburg when one little thing goes wrong. Thanks for your time, -- J.P. Larocque <jpl(a)thoughtcrime.us>

2 2

[postmodern-devel] simple-date:day-of-week doc mismatch?
by Daniel Janus 02 Apr '10

02 Apr '10

Hi, DB> (make-date nil nil nil) #<DATE 30-03-2010> DB> (day-of-week *) 1 That would indicate Monday, according to the docs, while my system calendar tells me it's Tuesday today... What gives? (I'm on SBCL 1.0.34.) Best regards, Daniel

2 1

[postmodern-devel] Release 1.16 (and new project page url, and new version control system)
by Marijn Haverbeke 02 Apr '10

02 Apr '10

Changes in this version: * Introduces a save-dao/transaction, because the old semantics were broken (originally inside of transactions, after fixing that outside of them). * Add support for passing vectors as argument to prepared queries, and reading them from query results. * Add :on-update and :on-delete arguments to !foreign. * Add :plist and :plists query result formats. * Guarantee that deftable definitions are executed in the order they were defined in. * Moves the ieee-floats and trivial-utf-8 dependencies into the repository, so they don't have to separately fetched. The project page is now no longer at common-lisp.net, but can instead be found at http://marijn.haverbeke.nl/postmodern . We will continue to use the common-lisp.net mailing lists. And finally, the darcs repository will no longer be updated. The new git repository lives at http://marijn.haverbeke.nl/postmodern/postmodern , and http://github.com/marijnh/Postmodern will be kept in sync with that. Best, Marijn

1 0

[postmodern-devel] Git?
by Marijn Haverbeke 27 Mar '10

27 Mar '10

Hello list, Would it cause problems, effort, or needless pain for any of you if I switched Postmodern development from darcs to git? I've been using git in pretty much all of my other work, and it's been so ingrained in my system that I'm starting to have trouble working with darcs. The whole project history can be converted using darcs-to-git. Also, I am planning to move over hosting of the project from common-lisp.net to my own server, since I have more control there (not to mention a better uptime record). If I make the switch to git, I'll probably make the server switch at the same time. Best, Marijn

4 3

[postmodern-devel] Developing with postmodern
by Mariano Montone 22 Mar '10

22 Mar '10

Hi! When developing with postmodern I'm having two problems: If a query is wrong, my slime connection seems to get disconnected or waiting for something (I have to hit C-c c to get the REPL back). Besides, when an error ocurrs, I don't get notified immediatly, but after I make another query, in which case I get a "the database is processing another query" (the screwed up query). So I have to restart the connection or the application (I'm developing a Web application)... :( Do someone else have the same problems? How do you fix them? Thanks! Mariano

3 13

[postmodern-devel] one-way array/record/coercion support
by Daniel Janus 22 Mar '10

22 Mar '10

Dear list, first of all, many thanks to Marijn and all the contributors for the excellent piece of code that is Postmodern. In my project I'm using it to connect to a database that heavily uses some of the advanced PostgreSQL functionality, including PL/pgSQL functions taking arrays of records as arguments. I found S-SQL to be a little lacking in expressiveness for that, so I've quickly hacked some methods that let me pass Lisp vectors to SQL and QUERY (they will be rendered into PostgreSQL array literals): ;;; code starts here (in-package :cl-postgres) (defmethod to-sql-string ((arg vector)) (cond ((typep arg '(vector (unsigned-byte 8))) (values (escape-bytes arg) t)) (t (format nil "ARRAY[~{~A~^, ~}]" (mapcar #'s-sql::sql-ize (coerce arg 'list)))))) (defmethod to-sql-string ((arg list)) (format nil "(~{~A~^, ~})" (mapcar #'s-sql::sql-ize arg))) (in-package :s-sql) (def-sql-op :coerce (form type) `(,@(sql-expand form) "::" ,type)) ;;; code ends here I'm sending it along in case it is useful to anyone else. Or perhaps it might make its way to the main repo? For the latter, presumably a full support would be needed, but I didn't find time to dive into Postmodern's way of passing back PostgreSQL-returned data into Lisp values of proper types, as I currently don't need that. Best regards, Daniel Janus

4 7

[postmodern-devel] Postgres transaction warnings and save-dao
by Peter Stiernström 09 Mar '10

09 Mar '10

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, I'm using postmodern with great success but I keep seeing these two warnings in my log: - "Postgres warning: there is no transaction in progress" - "Warning while processing connection: Postgres warning: there is already a transaction in progress" As far as I can tell these are merely warnings but they're filling up my logs so I had a look at what was causing it and I found that save-dao has a (with-transaction () ...) wrapping an insert-dao and I was calling save-dao from within my own transaction block. Now if just remove the with-transaction from save-dao an duplicate key violation will ruin the outer transaction by just aborting it (regardless of the error handling code. However if I add a savepoint just before trying insert-dao and in the error handling code use rollback-savepoint to restore to this savepoint I can make the warnings go away. The problem now being that save-dao expects to be called within a with-transaction form. So I was wondering if there is some way to tell if we are within a transaction (e.g. within-transaction-p)? Then I would be able to make a modification that handles both cases. It might then look something like this: (defun save-dao (dao) (if (within-transaction-p) (with-savepoint save-dao (handler-case (progn (insert-dao dao) t) (cl-postgres-error:unique-violation () (rollback-savepoint save-dao) (update-dao dao) nil)))) (handler-case (with-transaction () (insert-dao dao) t) (cl-postgres-error:unique-violation () (update-dao dao) nil))) -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkuWEs8ACgkQ0brSZD05ZzAj7wCfbhPynKyGMStzsRP/3MIGpOSH 6IcAni232P2lQ8/hAh5+03NmFr0FSbMs =EIRi -----END PGP SIGNATURE-----

3 5

[postmodern-devel] Release 1.15
by Marijn Haverbeke 01 Mar '10

01 Mar '10

I just bundled the changes (all relatively minor) made in the past year as release 1.15, so that those not pulling directly from darcs can also see them. Cheers, Marijn

1 0

Re: [postmodern-devel] Postmodern SBCL UNIX sockets
by Marijn Haverbeke 02 Feb '10

02 Feb '10

Hello Stian, I've pushed a patch to the repository that allows people to pass :unix as hostname on SBCL to cause cl-postgres to try and use a unix socket. I've also dropped the usocket dependency on that platform and switched to directly using sb-bsd-sockets, since usocket has half-a-zillion dependencies and doesn't really do anything useful -- there are only two places that have to be conditionalized, the connect call and the socket-error type. Best, Marijn

1 0

[postmodern-devel] Add :between-symmetric operator
by Daniel White 23 Jan '10

23 Jan '10

For example: (:select '* :from 'table :where (:between-symmetric 'column 10 1)) -- Daniel White

2 1

[postmodern-devel] creating a function based index
by Richard KLINDA 04 Jan '10

04 Jan '10

Hello, is there syntax available for creating function based indexes? How can I write this in s-sql? ,---- | CREATE INDEX my_index ON my_table (LOWER(my_column)) `---- I tried ,---- | (sql (:create-index :my-index :on :my-table :fields (:lower :my-column))) | | => (:LOWER :TERM) cannot be coerced to a string. | [Condition of type SIMPLE-TYPE-ERROR] `---- Thanks in advance, Richard

2 1

[postmodern-devel] Converting a SQL statement with subquery into S-SQL
by Sam Alves 26 Dec '09

26 Dec '09

Hi All, I'm trying to make the following SQL statement lispy by converting it to s-sql: SELECT * FROM filter WHERE title IN ( SELECT filter_title FROM user_filter WHERE user_id = 4); Here's my best attempt: :SELECT '* :FROM 'filter :WHERE 'title :IN (:SELECT 'filter_title :FROM user_filter :WHERE (:= 'user-id 3)) However, I get the following error: Keyword WHERE takes exactly one argument I've tried the SQL query in psql so I know it works, but I can't seem to come up with an equivalent s-sql statement. Thanks for any assistance, Sam -- Sam Alves Package Maintainer OSHEAN, Inc. Email: sam(a)oshean.org

2 2

[postmodern-devel] Exlcuding slots dao-save
by Phil Marneweck 10 Nov '09

10 Nov '09

Hi I am trying to figure out how to exclude certain slots when persisting the dao instance to the db short of making a new object with the limited range of slots. Any ideas or advise? Thanx

2 27

[postmodern-devel] Date Problem
by Phil Marneweck 04 Nov '09

04 Nov '09

I get different date formats back from a postmodern query at different times. I am using SBCL 1.0.31.32 and the latest (as of the weekend) from clbuild. The steps. 1. I delete all my .fasl files for a project 2. I do a require for the project to load and compile 3. I run a query/function and I get the correct "date" data back => #<DATE 01-01-2009> 4. I close emacs and re-open it 5. I do a require of the project to load 6. I run a query/function and I get incorrect "date" data back => 3439756800 Any ideas?

2 2