yumimueの日記

2007-10-31

[][]SQL変数を直接埋め込まない 16:36 はてなブックマーク - SQLに変数を直接埋め込まない - yumimueの日記

SQL変数を埋め込むと、エラーになる可能性があるし、セキュリティの点からもよろしくない。

SQL変数を埋め込む場合は、プレースホルダを使用する。

db.execute("insert into test values ('#{name}, '#{name}')") # ×
db.execute('insert into test values(?, ?)', name, mail)     # 〇

?の部分(プレースホルダ)が第二引数以降(バインド変数)で置き換えられる。その際、変数SQL特殊文字を含んでいても普通の文字として扱われる。


#!/usr/bin/ruby
require 'sqlite3'

db = SQLite3::Database.new('test.db')
print 'name:'; name = gets.chomp
print 'mail:'; mail = gets.chomp
begin
  db.execute("insert into test values ('#{name}, '#{name}')")
  puts 'insert ok'
rescue SQLite3::SQLException => e
  puts e 
end
begin
  db.execute("insert into test values (?, ?)", name, mail)
  puts 'insert ok'
rescue SQLite3::SQLException => e
  puts e
end
$ ruby sqlite_test.rb
name:hoge's
mail:hoge@hogehoge.com
near "s": syntax error
insert ok

[][]sqlite3-rubySQLiteをいじる 14:32 はてなブックマーク - sqlite3-rubyでSQLiteをいじる - yumimueの日記

sqlite-3-rubyのインストール

$ wget http://rubyforge.org/frs/download.php/17097/sqlite3-ruby-1.2.1.tar.gz
$ tar xzf sqlite3-ruby-1.2.1.tar.gz
$ cd sqlite3-ruby-1.2.1
$ ruby setup.rb config
$ ruby setup.rb setup
$ ruby setup.rb install

データベースへの接続

require 'sqlite3'
db = SQLite3::Database.new('test.db')

SQLiteではデータベース毎にファイルを一つ作成する。この場合、カレントディレクトリtest.dbというファイルをデータベースとしてオープンする。またファイルがない場合は新しく作成される。


SQL文の実行

テーブルの作成
sql = <<SQL
create table test (
  title varchar(255),
  url varchar(255)
);
SQL
db.execute(sql)

レコードの追加、変更、削除
db.execute("insert into test values ('yahoo', 'http://www.yahoo.co.jp')")
db.execute("update test set title='Yahoo' where title='yahoo'")
db.execute("delete from test where title='Yahoo'")

レコードとは関連するデータの集まり。上の例だとtitleとurlという2つのフィールドが集まって1件のレコードになる。


複数のSQLの実行

sql = <<SQL
insert into test values ('Yahoo', 'http://www.yahoo.co.jp');
insert into test values ('Google', 'http://www.google.co.jp');
SQL
db.execute_batch(sql)

SELECT文の実行結果を取得

SELECT文の実行結果はブロックを使って受け取ることができる。

db.execute('select * from test') do |r|
  p r
end
["Yahoo", "http://www.yahoo.co.jp"]
["Google", "http://www.google.co.jp"]

最初の列だけを取得
p db.get_first_row('select * from test') #=> ["Yahoo", "http://www.yahoo.co.jp"]

最初の値だけを取得
p db.get_first_value('select * from test') #=> "Yahoo"

例外処理

SQLのエラーは例外として補足できる。

db.execute('create table hoge(id)')
begin
  db.execute('create table hoge(id)')
rescue SQLite3::SQLException => e
  puts e #=> table hoge already exists
end