From: Dave Lilley <dglnz2@...>
Date: 2009-06-12T19:54:58+09:00
Subject: Re: SQLite3 passing  row data from 1 class to another

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 Candler 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
-- 
Posted via http://www.ruby-forum.com/.