[plt-scheme] Using prepared statements

From: David Einstein (deinst at gmail.com)
Date: Fri Jun 1 09:35:08 EDT 2007

On 5/31/07, Danny Yoo <dyoo at cs.wpi.edu> wrote:
> >>  Are there static type systems that can protect against, e.g., SQL
> >>  injection?
> >
> > I have always wondered why people aren't using Prepare more. Is it too
> > expensive? Or is the reason perhaps that people are using languages
> > without closures?
> I think it's ignorance.

Partially. There is also an element of something that lies on the continuum
between expedience and laziness.  Prepared statements are harder to write,
harder to read, and harder to maintain, mainly because the programmer is
forced to spend his or her limited attention counting question marks, but
also because there is more code involved.

I will freely admit to being one of those programmers that Matthias would
use OCAML to screen out (I'm not sure it would work), but I think that
blaming programmer ignorance for a problem that is essentially and impedance
mismatch between whatever language and SQL is the wrong thing to do
(particularly for people involved in programming language design.)   I think
that something like SchemeQL that can generate prepared statements and/or
write code to validate the input at compile time is a much better idea.
Computers are much better at counting question marks than I am.  Computers
are also much better at figuring out what goes in the WHERE clause and what
goes in the HAVING clause and all the other silly SQL hoo-ha.

As this discussion started out with typing, I must say that most of the type
errors that I perpetrate occur on the boundary between Java or C# and SQL,
mainly due to miscounted question marks.  This irks me because often the IDE
I am using knows which database I will run against, and could certainly
check the sanity of my input faster than I could.

When I was doing Python programming, I saw newcomers work with the Python
> DBI and almost always not use prepared statements but rather string
> concatenate.  That's what their teacher told them to do.  And even people
> who should know better can propagate this problem.  Here's one example:
>      http://mail.python.org/pipermail/tutor/2003-April/022010.html
> I saw the same thing with many Java programmers: I rarely saw use of
> prepareStatement(), and even when I saw people using it, the code would
> completely miss the point of prepareStatement(); I'd see code like:
>      stmt = conn.prepareStatement("insert into article (title) values ('"
> +
>                                   title + "')");
>      stmt.executeQuery();
> which made me laugh and cry at the same time.
> I'm very glad that Hans's SQLID interface includes support for prepared
> statements.  I would like to see it highlighted more vividly in the
> documentation.  As it is, it's just a quick mention in:
> http://www.elemental-programming.org/sqli.html#_code__sqli_query_sqli_handle_query___args____boolean__code_
> as a set of optional arguments passed to the "query" method.
> _________________________________________________
>   For list-related administrative tasks:
>   http://list.cs.brown.edu/mailman/listinfo/plt-scheme
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20070601/373e128a/attachment.html>

Posted on the users mailing list.