SQLite3 passing row data from 1 class to another

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:ininitialize’
from /usr/lib/ruby/1.8/sqlite3/database.rb:154:in new' from /usr/lib/ruby/1.8/sqlite3/database.rb:154:inprepare’
from /usr/lib/ruby/1.8/sqlite3/database.rb:181:in execute' from ./sqlite3_calls.rb:12:inrec_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.

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:

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.

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 C. 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://xkcd.com/327/

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.

Dave L. 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)

Brian C. 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?

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 C. 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
[email protected]:/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

Jarmo P. wrote:

Brian C. 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.