Asp Forum - SQLite3 passing row data from 1 class to another

Dave Lilley

6/11/2009 12:39:00 PM

I have code in one class that runs an SQL statement against a SQLite3
table and would like to return the resulting row back to another class
as an array.

here is my 'database' class....

require "sqlite3"

class Dbase
def initialize
@db = SQLite3::Database.new( "customer" )
@db.results_as_hash = true

end

def rec_to_find (table, colname, tofind)
stmt = "select * from #{table} where #{colname} = " + tofind
row = @db.execute(stmt)
result row
end

end

I am having trouble in getting the resulting row (IT WILL always be
either 1 row or no row being returned).

this is the main program for what it is

require "sqlite3_calls"

db = Dbase.new

puts 'customer name to find'
cust_nos = gets.chomp

row = db.rec_to_find("customers", "custnos", cust_nos)

puts row

and these are the errors i currently get,

/customer_test$ ruby cust2.rbcustomer name to find
1
/usr/lib/ruby/1.8/sqlite3/errors.rb:62:in `check': no such column:
custnos(SQLite3::SQLException)
from /usr/lib/ruby/1.8/sqlite3/statement.rb:39:in `initialize'
from /usr/lib/ruby/1.8/sqlite3/database.rb:154:in `new'
from /usr/lib/ruby/1.8/sqlite3/database.rb:154:in `prepare'
from /usr/lib/ruby/1.8/sqlite3/database.rb:181:in `execute'
from ./sqlite3_calls.rb:12:in `rec_to_find'
from cust2.rb:8

as mentioned above I will want the data back in as an array for further
processing on individual fields, currently i cannot achieve this and be
grateful for some help.

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

4 Answers

Brian Candler

6/11/2009 3:48:00 PM

You need to read the error message! It's very clear what it is telling
you:

> /usr/lib/ruby/1.8/sqlite3/errors.rb:62:in `check': no such column:
> custnos(SQLite3::SQLException)

That is, there is an error in your SQL. You are trying to do something
with a column called "custnos", and your table does not have one.

The sqlite3 command line tool is useful here:

sqlite3 path/to/your/db.sqlite3

This lets you try out SQL interactively, until you arrive at the
incantation which works.

> def rec_to_find (table, colname, tofind)
> stmt = "select * from #{table} where #{colname} = " + tofind
> row = @db.execute(stmt)
> result row
> end

This code is broken because you tack tofind onto the end of your query
without quoting it. Imagine you do the following:

rec_to_find("customers","name","fred")

This will generate:

select * from customers where name=fred

This is almost certainly not what you want (this query finds customers
where the value in column 'name' is the same as the value in column
'fred')

What you probably wanted was:

select * from customers where name='fred'

However, just adding the quotes in by itself is also very dangerous.
Suppose someone enters a customer name which contains a single-quote;
you can end up with (best case) a corrupt SQL statement, or (worst case)
you have allowed the user to add or modify *all* the data in your
database with a carefully-constructed 'name' value.

This is illustrated beautifully here:
http://xkc...

If you don't understand this, then you should steer clear of
constructing SQL queries. Instead, use an abstraction layer to handle
this for you. For example, with ActiveRecord you can write

n = gets.chomp
Customer.find(:all, :conditions => ["name = ?", n])
or
n = gets.chomp
Customer.find(:all, :conditions => {:name => n})

and it will take care of all the SQL building and escaping for you.
--
Posted via http://www.ruby-....

Dave Lilley

6/12/2009 10:01:00 AM

many thanks Brian must have been tried lastnight,

SQLite3 is new to me and i want to get up and running fast so I thought
using DBI would be better than ActiveRecord (this would then be my next
step).

I'm wanting to get a prototype up to show a friend what he want using
ruby (namely a CRM type app) - no real check etc just a working demo.

I did use this code originally but I want to be able to have the data
returned to as an array so tried and posted what was below.

so this any better (with regard to sql injection?)

My cust.rb script

require "sqlite3_calls"

db = Dbase.new

puts 'customer name to find'
cust_nos = gets.chomp

# these 2 line have changed from original post
row = db.rec_to_find("customers", "cust_nos", "#{cust_nos}")

row.each{|t| puts "line 1 = " + t}

sqlite3_calls.rb script

require "sqlite3"

class Dbase
def initialize
@db = SQLite3::Database.new( "customer" )

# my feeble attempt at getting an array pasted - thought it might've
help me in
# someway to do it without creating code to do a csv string manually as
hinted
# at further down in a comment line.

@db.results_as_hash = true
end

def rec_to_find (table, colname, tofind)

# this line has changed from original post
stmt = "select * from #{table} where #{colname} = #{tofind}"
row = @db.execute(stmt)

# would like to return an array object of the record found for further
# processing looking at do a row.each {|fld| rec_array = fld+',' } thus
# achieving a comma delimited string with which i can do a split on to
get at
# each field i want but think there has to be a better way - I just
don't know
# how to at present.

row
end

end

your opinion would be gratefully appreciated.

Dave.

Brian Candler wrote:
> You need to read the error message! It's very clear what it is telling
> you:
>
>> /usr/lib/ruby/1.8/sqlite3/errors.rb:62:in `check': no such column:
>> custnos(SQLite3::SQLException)
>
> That is, there is an error in your SQL. You are trying to do something
> with a column called "custnos", and your table does not have one.
>
> The sqlite3 command line tool is useful here:
>
> sqlite3 path/to/your/db.sqlite3
>
> This lets you try out SQL interactively, until you arrive at the
> incantation which works.
>
>> def rec_to_find (table, colname, tofind)
>> stmt = "select * from #{table} where #{colname} = " + tofind
>> row = @db.execute(stmt)
>> result row
>> end
>
> This code is broken because you tack tofind onto the end of your query
> without quoting it. Imagine you do the following:
>
> rec_to_find("customers","name","fred")
>
> This will generate:
>
> select * from customers where name=fred
>
> This is almost certainly not what you want (this query finds customers
> where the value in column 'name' is the same as the value in column
> 'fred')
>
> What you probably wanted was:
>
> select * from customers where name='fred'
>
> However, just adding the quotes in by itself is also very dangerous.
> Suppose someone enters a customer name which contains a single-quote;
> you can end up with (best case) a corrupt SQL statement, or (worst case)
> you have allowed the user to add or modify *all* the data in your
> database with a carefully-constructed 'name' value.
>
> This is illustrated beautifully here:
> http://xkc...
>
> If you don't understand this, then you should steer clear of
> constructing SQL queries. Instead, use an abstraction layer to handle
> this for you. For example, with ActiveRecord you can write
>
> n = gets.chomp
> Customer.find(:all, :conditions => ["name = ?", n])
> or
> n = gets.chomp
> Customer.find(:all, :conditions => {:name => n})
>
> and it will take care of all the SQL building and escaping for you.

--
Posted via http://www.ruby-....

Dave Lilley

6/12/2009 10:55:00 AM

Bad form i know....

my curiosity got the better of me regarding my desire to pass a database
row in a class back to the main program.

Brian Candler has commented about my exposure to sql inject and how bad
it can be and i know i only have passing knowledge of SQL and is by no
means indepth.

this is the current code and the resulting output to my screen.
I would like suggestions how how to improve it so as it stops sql
injection.

Active Record will be the next step for me but as i am interested in
getting a working demo to show him i can enter data in extract data from
the database so am happy to use DBI at the moment.

code & out put are below.

require "sqlite3"

class Dbase
def initialize
@db = SQLite3::Database.new( "customer" )

end

def rec_to_find (table, colname, tofind)
stmt = "select * from #{table} where #{colname} = #{tofind}"
row = @db.execute(stmt)
@rec = []
row.each do|fld|
@rec = fld
end

return @rec

end

end

require "sqlite3_calls"

db = Dbase.new

puts 'customer name to find'
cust_nos = gets.chomp

row = db.rec_to_find("customers", "cust_nos", "#{cust_nos}")

row.each{|t| puts "in cust 2 script & field = #{t}"}

output is here
dave@main-pc:/customer_test$ ruby cust2.rb
customer name to find
1
in cust 2 script & field = 1
in cust 2 script & field = 11 happy dtreet
in cust 2 script & field = cust_1
in cust 2 script & field = cust 1 name
in cust 2 script & field = 1
in cust 2 script & field = this is the first customer
we have in teh data base!
and this should appear in a edit box
as multiple lines - 4 to be precise
in cust 2 script & field = n
in cust 2 script & field = 12345678

fields are ...
customer number text
address text
customer name text
contact text
id primary index
notes blob
on stop numeric - boolean field really
phone number numeric

as i said no validation is done and the data is just something I've put
in to see an output from.

My initial question has now been solved but would like clarification on
how to reduce or stop SQL INJECTION.

for the foreseeable future there will be no internet access and only 1
user using this program but being able to stop or reduce sql injection
would be appreachiated.

Upon this matter i was wondering if i used accessors in place of the
method parameters if that would stop or reduce the risk or is this
silly?

Cheers,

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

Brian Candler

6/12/2009 5:15:00 PM

Dave Lilley wrote:
> My initial question has now been solved but would like clarification on
> how to reduce or stop SQL INJECTION.

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)
--
Posted via http://www.ruby-....

comp.lang.ruby

SQLite3 passing row data from 1 class to another

Dave Lilley

Brian Candler

Dave Lilley

Dave Lilley

Brian Candler

x Login to ForumsZone