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

What you've done is what I suggested in the tutorial, I'd suggest the
Wikipedia entry for a simple introduction to injection and how
parameterized statements help:

http://en.wikipedia.org/wiki/SQL_injection

Jay

On Fri, Jul 16, 2010 at 3:17 PM, Horace Dynamite
<horace.dynamite at gmail.com> wrote:
> 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.
> _________________________________________________
>  For list-related administrative tasks:
>  http://lists.racket-lang.org/listinfo/users
>



-- 
Jay McCarthy <jay at cs.byu.edu>
Assistant Professor / Brigham Young University
http://teammccarthy.org/jay

"The glory of God is Intelligence" - D&C 93