Module: JDBCHelper::SQL

Defined in:
lib/jdbc-helper/sql.rb

Defined Under Namespace

Classes: Expr, NotNilClass

Class Method Summary collapse

Class Method Details

.check(expr, is_name = false) ⇒ Object

FIXME: Naive protection for SQL Injection

Raises:

  • (ArgumentError) 


91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/jdbc-helper/sql.rb', line 91

def self.check expr, is_name = false
  return nil if expr.nil?

  tag = is_name ? 'Object name' : 'Expression'
  test = expr.gsub(/'[^']*'/, '').gsub(/`[^`]*`/, '').gsub(/"[^"]*"/, '').strip
  raise ArgumentError.new("#{tag} cannot contain (unquoted) semi-colons: #{expr}") if test.include?(';')
  raise ArgumentError.new("#{tag} cannot contain (unquoted) comments: #{expr}") if test.match(%r{--|/\*|\*/})
  raise ArgumentError.new("Unclosed quotation mark: #{expr}") if test.match(/['"`]/)
  raise ArgumentError.new("#{tag} is blank") if test.empty?

  if is_name
    raise ArgumentError.new(
      "#{tag} cannot contain (unquoted) parentheses: #{expr}") if test.match(%r{\(|\)})
  end

  return expr
end

.count(table, conds = nil) ⇒ Object

Generates count SQL with the given conditions



81
82
83
 
# File 'lib/jdbc-helper/sql.rb', line 81

def self.count table, conds = nil
  check "select count(*) from #{table} #{where_internal conds}".strip
end

.delete(table, conds = nil) ⇒ Object

Generates delete SQL with the given conditions



86
87
88
 
# File 'lib/jdbc-helper/sql.rb', line 86

def self.delete table, conds = nil
  check "delete from #{table} #{where_internal conds}".strip
end

.expr(str) ⇒ Object

Prevents a string from being quoted



7
8
9
 
# File 'lib/jdbc-helper/sql.rb', line 7

def self.expr str
  Expr.new str
end

.insert(table, data_hash) ⇒ Object

Generates insert SQL with hash



49
50
51
 
# File 'lib/jdbc-helper/sql.rb', line 49

def self.insert table, data_hash
  insert_internal 'insert', table, data_hash
end

.insert_ignore(table, data_hash) ⇒ Object

Generates insert ignore SQL (Non-standard syntax)



54
55
56
 
# File 'lib/jdbc-helper/sql.rb', line 54

def self.insert_ignore table, data_hash
  insert_internal 'insert ignore', table, data_hash
end

.not_nil ⇒ Object

Returns NotNilClass singleton object



12
13
14
 
# File 'lib/jdbc-helper/sql.rb', line 12

def self.not_nil
  NotNilClass.singleton
end

.order_by(*criteria) ⇒ Object

Generates SQL order by cluase with the given conditions.



40
41
42
43
 
# File 'lib/jdbc-helper/sql.rb', line 40

def self.order_by *criteria
  str = criteria.map(&:to_s).reject(&:empty?).join(', ')
  str.empty? ? str : check('order by ' + str)
end

.replace(table, data_hash) ⇒ Object

Generates replace SQL (Non-standard syntax)



59
60
61
 
# File 'lib/jdbc-helper/sql.rb', line 59

def self.replace table, data_hash
  insert_internal 'replace', table, data_hash
end

.select(table, conds = nil, orders = nil) ⇒ Object

Generates select * SQL with the given conditions



72
73
74
75
76
77
78
 
# File 'lib/jdbc-helper/sql.rb', line 72

def self.select table, conds = nil, orders = nil
  check [
    "select * from #{table}", 
    where_internal(conds),
    order_by(orders)
  ].reject(&:empty?).join(' ')
end

.update(table, data_hash) ⇒ Object

Generates update SQL with hash. :where element of the given hash is taken out to generate where clause.



65
66
67
68
69
 
# File 'lib/jdbc-helper/sql.rb', line 65

def self.update table, data_hash
  where_clause = where_internal(data_hash.delete :where)
  updates = data_hash.map { |k, v| "#{k} = #{value v}" }.join(', ')
  check "update #{table} set #{updates} #{where_clause}".strip
end

.value(data) ⇒ Object

Formats the given data so that it can be injected into SQL



17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
# File 'lib/jdbc-helper/sql.rb', line 17

def self.value data
  case data
  when NilClass
    'null'
  when Fixnum, Bignum, Float
    data
  when JDBCHelper::SQL::Expr
    data.to_s
  when String
    "'#{esc data}'"
  else
    raise NotImplementedError.new("Unsupported datatype: #{data.class}")
  end
end

.where(conds) ⇒ Object

Generates SQL where cluase with the given conditions. Parameter can be either Hash of String.



34
35
36
37
 
# File 'lib/jdbc-helper/sql.rb', line 34

def self.where conds
  where_clause = where_internal conds
  where_clause.empty? ? where_clause : check(where_clause)
end