From: Dave Lilley <dglnz2@...>
Date: 2009-06-17T17:47:13+09:00
Subject: Re: SQLite3 passing  row data from 1 class to another

Jarmo Pertman wrote:
> Brian Candler wrote:
>> There is most likely a quoting function provided as part of the sqlite3 
>> API. You need to read the API docs. But I still think that you're better 
>> off using a higher-level abstraction library (ActiveRecord, DataMapper, 
>> Sequel, og ... choose whatever suits you best)
> You can use placeholders in SQLite3 like this:
> http://sqlite-ruby.rubyforge.org/sqlite3/faq.html#538670816
> 
> Why it's always better to use some higher-level (heavyweight) 
> abstraction library?

@Jarmo,

Many thanks for the reply,

I decided to post this to SQLite3 forum as i thought it would be more 
appropriate.

I could not understand how the bind worked (even though i had visited 
the url you gave), but now i do.

Hope this bit will give a much clear explanation (more for anyone else 
who amy do a search on SQLite3 and binding within this forum.

Note the following is from a reply I got on the SQLite3 list to a 
question i had about stopping SQL injection.

> 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?
>
> 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?
>
>

No.  You would have to use the table and field names directly:

def make_SQL(table, field, criteria)
  stmt = "select * from #{table} where #{field} = ?"
  row = db.execute(stmt, criteria) << this makes it SAFE from SQL 
injection
end


so a user could enter delete * where customer_nos = 100 and the SQL 
injection would fail on the code
             row = db.execute(stmt, criteria)
because criteria is take as a string and not part of the SQL query.

the only thing that would happen with my understanding is that nil would 
be returned.

It was suggested that the passing of the table and field would be okay 
as long as it was passed via the program (aka coded as a passed 
parameter and not something a user could touch under normal 
circumstances.

thnkas all for your help.

Dave.
-- 
Posted via http://www.ruby-forum.com/.