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

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