| Description: |
Changes from previous version 4.2006.04.19:
If you use * as a key, then keys_of() doesn't put * in
between backticks (if backticks enabled). Previous versions
produced "SELECT `*` FROM ..." for example.
Description
Build safe sql query from a data hash.
You don't have to take care about escaping values.
You don't have to write code iterating over arrays to produce comma separated lists (for WHERE, SET, etc.)
Usage
# 1. Create a data hash.
hsh = {
:bad_characters => 'very bad characters: !@#$@#%&*\"\' ',
:tblname => 'my_foo_table',
:mydata => {
:a => 'AAA',
:b => '',
:c => nil,
:d => 'DDD',
:e => false
},
:emptydata => { }
}
# 2. Create SafeQuery4 instance; FooDB is used because of the 'escape_string' method. You can specify another name of the method as the second parameter.
sq = SafeQuery4.new( FooDB.new, hsh )
# 3. Finally create safe queries!
puts "Everything is automatically escaped: "
puts " INSERT INTO #{sq.tblname} ( very_bad_characters ) VALUES ( '#{sq.bad_characters}' ) "
puts "More examples: "
puts " INSERT INTO #{sq.tblname} (#{sq.keys_of :mydata }) VALUES (#{sq.values_of :mydata }) "
puts " SELECT * FROM #{sq.tblname} WHERE #{sq.LIKE_list_of :mydata } "
puts " SELECT * FROM #{sq.tblname} WHERE #{sq.LIKE_list_of :mydata, :separator=>'OR' } "
puts "More examples - using except filter: "
puts " UPDATE #{sq.tblname} SET #{sq.pairs_of :mydata, :except=>[:nil, :false] } "
puts " INSERT INTO #{sq.tblname} (#{sq.keys_of :mydata, :except=>[:nil, :empty] }) VALUES (#{sq.values_of :mydata, :except=>[:nil, :empty] }) "
puts " SELECT * FROM #{sq.tblname} WHERE #{sq.LIKE_list_of :mydata, :except=>[ :false, :nil ] } "
puts "You can use default which will be used if result is empty string"
puts " SELECT FROM #{sq.tblname} WHERE (#{sq.pairs_of :emptydata, :default=>1 }) "
puts "If you want you can use keys in backticks."
sq.use_backticks = true
puts " SELECT FROM #{sq.tblname} WHERE (#{sq.pairs_of :mydata, :except=>[:nil,:false]}) "
|
|---|