On Mon, Jun 15, 2009 at 07:09:44PM +1200, dave lilley scratched on the wall:
> >>e.g. stmt = "select * from customers where cust_no = #{uservar}"
> >> row = db.execute(stmt)
>
> Now i could understand how my code could possibly allow the records in the
> table to be deleted but what's the difference to the code you gave me?
The difference is that, in the second case (below), the value of
"uservar" is never passed through the SQL parser, making it impossible
to inject SQL statements. The values are not "escaped", they fully
circumvent the SQL statement processing step and are bound directly
to the statement logic.
See http://sqlite.org/lang_expr.html#varparam for more specifics
about how this works with the C API. The same basic rules apply to
whatever system you're using.
> stmt = "select * from customers where cust_no = ?"
> row = db.execute(stmt, uservar)
On Mon, Jun 15, 2009 at 07:02:16PM +1200, dave lilley scratched on the wall:
> Many thanks John so if i take that example and push it out so i can have 1
> method that can return a SQL select statement on any table, field and search
> criteria i would only need to do this?
No. You can only use place holders as stand-ins for literal values.
So any place you can put an expression, you can put a place holder.
You cannot use place holders for identifiers, which include both table
names and column names.
So in this example, you can replace "criteria" but not "table" or
"field". I suppose you could do it "the ruby way" but most APIs
should have some way to prepare and hold onto statements so that they
can be used more than once. That would largely invalidate "the ruby
way."
> In ruby it would be ....
>
> make_SQL (table, field, criteria)
> stmt = "select * from #{table} where #{field} = #{criteria}"
> row = db.execute(stmt)
> end
>
> and SQLite3 way would be ...
>
> make_SQL(table,field,criteria)
> stmt = "select * from ? where ? = ?"
> row = db.execute(stmt)
> end
>
> would this presumtion be correct?
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
