class MYSQLSafe::Base

Attributes

database[RW]
encoding[RW]
host[RW]
password[RW]
user[RW]

Public Instance Methods

connect_safe(raw_sql) click to toggle source
# File lib/MYSQLSafe/base.rb, line 7
def connect_safe(raw_sql)
        @mysql_array = []
        @encoding ||= 'utf-8'
        options = {}
        self.instance_variables.map{|name| options = options.merge({ name.to_s.delete("@") => self.instance_variable_get(name) }) }
        options.each do |k,v|
                options[k] = esc_enc_string(v)
        end
        
        
        sql = enc_string(raw_sql)
        begin
                case
                        when options["host"], options["user"], options["password"], options["database"]
                                @cxtn = Mysql.new(options["host"], options["user"], options["password"], options["database"])
                        when options["host"], options["user"], options["password"]
                                @cxtn = Mysql.new(options["host"], options["user"], options["password"])
                        when options["host"], options["user"]
                                @cxtn = Mysql.new(options["host"], options["user"])
                        else
                                raise "MYSQLSafe error: In order to connect to MYSQL you must at least set the host and username. So far you have included #{options}."
                end
                
                table_names = get_table_names
                table_match = match_name(table_names, sql)
                
                if table_match
                        column_names = get_column_names(table_match)
                        column_match = match_name(column_names, sql)
                        column_match = [] if !(sql.to_s.downcase.include?('where'))
                else
                        raise 'MYSQLSafe error: no valid table name could be found in your SQL statement'
                end
                
                if column_match
                        ticked_sql = tick_sql(sql, table_match, column_match)
                else
                        raise 'MYSQLSafe error: no valid column name(s) could be found in your SQL statement'
                end
                
                mysql_object = @cxtn.query(ticked_sql)
                mysql_object.each { |row| @mysql_array.push(row) } if mysql_object
                unless @mysql_array.size > 0
                        @mysql_array = ["Success, with 'nil' result"] 
                end
        rescue Mysql::Error => msqle
                puts "Error! #{msqle}, #{@mysql_array}"
                @mysql_array.push(["MYSQL Error: #{msqle}"])
        ensure
                @cxtn.close if @cxtn
        end
        
        return @mysql_array
                
        end

Private Instance Methods

enc_string(string) click to toggle source
# File lib/MYSQLSafe/base.rb, line 131
def enc_string(string)
        return string.encode("#{@encoding}", "#{@encoding}", :invalid => :replace)
end
esc_enc_string(string) click to toggle source
# File lib/MYSQLSafe/base.rb, line 127
def esc_enc_string(string)
        return esc_string(enc_string(string.to_s))
end
esc_string(string) click to toggle source
# File lib/MYSQLSafe/base.rb, line 135
def esc_string(string)
        return Mysql.escape_string(string)
end
get_column_names(table_names) click to toggle source
# File lib/MYSQLSafe/base.rb, line 76
def get_column_names(table_names)
        column_names = []
        table_names.each do |table_name|
                column_names_sql = "SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='#{@database}' AND `TABLE_NAME`='#{table_name}';"
                column_names_results_sql = @cxtn.query(column_names_sql)
        
                column_names_results_sql.each do |name|
                        column_names.push(name[0])
                end
        end
        
        return column_names
end
get_table_names() click to toggle source
# File lib/MYSQLSafe/base.rb, line 115
def get_table_names
        table_names_sql = "SHOW TABLES FROM `#{@database}`;"
        table_names_results_sql = query_safe(table_names_sql)
        
        table_names = []
        table_names_results_sql.each do |name|
                table_names.push(name[0])
        end
        
        return table_names
end
match_name(name_array, sql) click to toggle source
# File lib/MYSQLSafe/base.rb, line 90
def match_name(name_array, sql)
        match = []
        arrayable_sql = sql

        name_array.each do |name|
                containing_characters = ["\"", "'", "`", ",", "(", ")", ".", "=", ";"]
                containing_characters.each do |illegal|
                        arrayable_sql = arrayable_sql.gsub(illegal, ' ')
                end
                arrayable_sql.split(" ").each do |word|
                        match.push(name) if word == "#{name.strip}"
                end
        end
        
        if match.size > 0
                return match
        else
                return false
        end
end
query_safe(dangerous_sql) click to toggle source
# File lib/MYSQLSafe/base.rb, line 111
def query_safe(dangerous_sql)
        @cxtn.query(Mysql.escape_string(dangerous_sql))
end
tick_sql(sql, table_array, column_array) click to toggle source
# File lib/MYSQLSafe/base.rb, line 64
def tick_sql(sql, table_array, column_array)
        ticked_sql = sql.delete("`")
        table_array.uniq.each do |name|
                ticked_sql = ticked_sql.gsub(name, "`#{name}`")
        end
        column_array.uniq.each do |col|
                ticked_sql = ticked_sql.gsub(col, "`#{col}`")
        end
        
        return ticked_sql
end