From: Brian Candler <b.candler@...>
Date: 2009-06-12T00:47:51+09:00
Subject: Re: SQLite3 passing  row data from 1 class to another

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