[racket] Prepared SQL statements, am I doing it right? | continue blog app.

From: Horace Dynamite (horace.dynamite at gmail.com)
Date: Fri Jul 16 17:17:50 EDT 2010

Dear readers,

I'm playing around with the "continue" blog app, which I've found on
the front page of the help desk. In the section about using a SQL
database, there is the following paragraph,

"This is called an SQL injection attack. It can be resolved by using
prepared statements that let SQLite do the proper quoting for us.
Refer to the SQLite package documentation for usage."

I found what I believe to be the package documentation the authors
refer to here,
http://planet.plt-scheme.org/package-source/jaymccarthy/sqlite.plt/4/0/planet-docs/sqlite/index.html

I'm not sure if my additions have protected me against this SQL
injection attack.

To be able to prepare the statements for my blog database, I've
changed the model interface, by defining the blog in the model file
(rather than the blog app), and exposing it via the models provide
form to the blog app,

;; The blog
(define model-blog (initialize-blog! (build-path BLOG-ROOT "blog.db")))

(provide blog? blog-posts
         post? post-title post-body post-comments
         model-blog ; <-- used to be initialize-blog!
         blog-insert-post! post-insert-comment!)

and the in the blog app, I have changed the start procedure to this,

; start: request -> html-response
; Consumes a request, and produces a page that displays all of the
; web content.
(define (start request)
  (render-blog-page
   model-blog ; <-- from model
   request))

Here are the prepared SQL statements for post-insert-comment! and
blog-insert-post! (back in model file now)

;; SQL preparations
(define sql-insert-post-stmt
  (sqlite:prepare (blog-db model-blog) "INSERT INTO posts (title,
body) VALUES (?, ?)"))
(define sql-insert-comment-stmt
  (sqlite:prepare (blog-db model-blog) "INSERT INTO comments (pid,
content) VALUES (?, ?)"))

And then I've modified the post-insert-comment! and blog-insert-post!
procedure like so,

;; blog-insert-post! : blog string string -> void
;; To insert a post to the front of a-blog
(define (blog-insert-post! blog title body)
  (sqlite:run sql-insert-post-stmt
              title body))

;; post-insert-comment! : blog post string -> void
;; To add a comment to the comment list in post
(define (post-insert-comment! blog post comment)
  (sqlite:run sql-insert-comment-stmt
              (post-id post) comment))

Have I protected myself from this attack now? I'm most confused by the
statement "SQLite can do the proper quoting for us". I don't
understand how my changes have accommodated this.

Thanks very much for your time,

Horace.


Posted on the users mailing list.