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