Simple DB Access

[Christian S]

On 8/2/06, Ryan Rawson <ryanobjc@REDACTED> wrote:
> Being a long time developer on Oracle I now don't understand this
> 'prepare' concept.  Every single SQL statement that takes in user
> input should use bind variables.  If this makes you have to prepare
> statements, then so be it (our oracle API doesnt require prepares, but
> forces us to use binds).

JDBC, java's sql api, only have the PreparedStatement class if one want to
bind rather than construct SQL by string concatenation. I guess
binding is a more
accurate name for what I meant. Anyway, the postgres protocol, called
FE/BE: http://developer.postgresql.org/docs/postgres/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

FE/BE in extended queries will first parse a string and refers to the
result as a prepared statement. It can then be reused multiple time
using binding. Binding one of these prepared statements yields, what
the pg documentation calls, a 'portal'. The portal can then be
executed. I'm not exactly sure why this extra step is a good idea, but
it is the way it is.

> With binds you don't have SQL injection holes.  It's as simple as that.
>
> Say no to constructed SQL.

My line exactly. Too bad I havent figured out a way to make it harder
to construct strings than using binding. Maybe if this edbc
application itself required statements to be registered by a name, and
then executed by name. Something like

ok = edbc:register_statement(Db, foo, "SELECT foo FROM foobars WHERE
foo = "++Bar),
case edbc:execute_statement(Db, foo) of
  PossibleResult -> ...
end,

And the binding version would be
ok = edbc:register_statement(Db, prepared, "SELECT foo FROM foobars
WHERE foo = ?foo"),
case edbc:execute(Db, foo, [{foo, "bar"}]) of
  PossibleResult ->
end

About the syntax for the "to-be-bounds" in statements. They vary from
rdbm to rdbm, i used "?foo" other use "?" and refer to them by
occuring order number, and then what?. To make it coherent across
several databases one wants to parse the sql query to find them, and
then produce a string again, a string that the target database's
parser will understand.
I think, how do other language's sql api deal with it?