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