# SQLite3 passing row data from 1 class to another

**URL:** <https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026>\
**Category:** Ruby\
**Created:** [June 11, 2009, 2:38pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026 "2009-06-11T14:38:34Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 11, 2009, 2:38pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/1 "2009-06-11T14:38:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 11, 2009, 5:47pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/2 "2009-06-11T17:47:00Z")

</div>

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:

> **[Exploits of a Mom](https://xkcd.com/327/)**
>
> Her daughter is named Help I'm trapped in a driver's license factory.

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.

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 12, 2009, 12:01pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/3 "2009-06-12T12:01:10Z")

</div>

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:  
> [xkcd: Exploits of a Mom](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.

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 12, 2009, 7:15pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/4 "2009-06-12T19:15:13Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 17, 2009, 10:25am UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/5 "2009-06-17T10:25:26Z")

</div>

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](http://sqlite-ruby.rubyforge.org/sqlite3/faq.html#538670816)

Why it’s always better to use some higher-level (heavyweight)  
abstraction library?

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 12, 2009, 12:54pm UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/6 "2009-06-12T12:54:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paradise](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/8e7dd6/32.png) [@paradise](https://www.ruby-forum.com/u/paradise)\
**Post date:** [June 17, 2009, 10:47am UTC](https://www.ruby-forum.com/t/sqlite3-passing-row-data-from-1-class-to-another/170026/7 "2009-06-17T10:47:05Z")

</div>

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](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.