From: "Ara.T.Howard" <ahoward@...>
Date: 2004-06-29T12:42:58+09:00
Subject: Re: SQLite-Ruby and "other chrs"

On Sat, 26 Jun 2004, Meino Christian Cramer wrote:

> Hi,
>
> a problem with "other chars" hits me... :)
>
> I try to read a database of shortwave broadcasters (formatted ascii)
> into a SQLIte-Ruby database.
>
> The initialization is ok, but from time to time the population of the
> db breaks, cause some broadcasters station names include invalid
> chars like
>
> 	   R.N'Djamena
>
> and the "'" kills everything.
>
> Escaping that character in the textfile, which is read to populate
> the db, doesn't work, cause "\'" ist read as "\" + "'", which
> again is one "'" to much.
>
> With mysql there was a method called ".Mysql_escape_string" which
> does this job. In the SQLite and SQLite-Ruby docs I haven't found
> anything similiar...
>
> How can I solve this problem?
>
> Kind regards and thank you very much in advance,
> Meino

i'd forgotten i had this - it's old but it should work:

===============================================================================
file: quote.rb
===============================================================================
#
# a module for quoting sql database entries
#
# eg. 
#
#   tuple = %w( it's hard to quote this )
#   values = Quote.q tuple
#   sql = "insert into tbl values ( #{ values.join ',' } );"
#
#   => insert into tbl values ( 'it''s','hard','to','quote','this' );
#
   module Quote
#{{{
   #
   # escapes (with esc) all occurances of char in s, modifying s in place
   #
     def escape! s, char, esc
#{{{
       re = %r/([#{0x5c.chr << esc}]*)#{char}/
       s.gsub!(re) do
         (($1.size % 2 == 0) ? ($1 << esc) : $1) + char
       end
#}}}
     end
     module_function 'escape!'
     public 'escape!'
   #
   # escapes (with esc) all occurances of char in s, returning a new str
   #
     def escape s, char, esc
#{{{
       ss = "#{ s }"
       escape! ss, char, esc
       ss
#}}}
     end
     module_function 'escape'
     public 'escape'
   #
   # given an object, return a single quoted str of this object, escaping all
   # occurances of ' iff esc is true (default).  this method recognizes Array
   # objects as special cases and will recurse over any sub Arrays untill all
   # sub objects are single quoted
   #
   # p (q [42, 'ford']) # >> ["'42'", "'ford'"]
   #
     def q object, esc = true, accum = nil
#{{{
       quote object, esc, "'", accum
#}}}
     end
     module_function 'q'
     public 'q'
   #
   # same a q but with double quotes
   #
     def qq object, esc = true, accum = nil
#{{{
       quote object, esc, '"', accum
#}}}
     end
     module_function 'qq'
     public 'qq'
   #
   # impl of q and qq
   #
     def quote object, esc = true, q = "'", accum = nil
#{{{
       if Array === object
         if accum
           accum << []
           accum = accum[-1]
         else
           accum = []
         end
         object.each{|object| quote object, esc, q, accum}
         return accum
       end

       quoted =
         if esc
           "#{ q }#{ escape object, q, q }#{ q }"
         else
           "#{ q }#{ object }#{ q }"
         end

       if accum
         accum << quoted
       else
         quoted
       end
#}}}
     end
     module_function 'quote'
     public 'quote'
#}}}
   end
#
# sample usage
#
if $0 == __FILE__
#{{{

#
# simple
#
    tuple = %w( it's hard to quote this )
    values = Quote.q tuple
    sql = "insert into tbl values ( #{ values.join ',' } );"
    puts sql
    puts

#
# less simple
#

   include Quote

   tests =
     [
       [%Q|a\\\"bc|, '"', '\\'],
       [%Q|a\\\\\"bc|, '"', '\\'],
       [%q|a'b'c|, "'", '\\'],
       [%q|a#b#c|, '#', '\\'],
     ]

   tests.each do |test|
     s, char, esc = test
     e = escape s, char, esc
     puts "s : <#{ s }>"
     puts "e : <#{ e }>"
     puts
   end


   quoted = q 'hi'
   puts quoted

   quoted = q ['hi', 'hello']
   puts(quoted.join(' '))

   quoted = q [['hi', 'hello'],['howdy','hullo']]
   puts(quoted[0].join(' '))
   puts(quoted[1].join(' '))

   quoted = q [[['hi', 'hello'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = q [[['h\'i', 'he\'llo'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = qq 'hi'
   puts(quoted)

   quoted = qq ['hi', 'hello']
   puts(quoted.join(' '))

   quoted = qq [['hi', 'hello'],['howdy','hullo']]
   puts(quoted[0].join(' '))
   puts(quoted[1].join(' '))

   quoted = qq [[['hi', 'hello'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))

   quoted = qq [[['h"i', 'hel"lo'],['howdy','hullo']]]
   puts(quoted[0][0].join(' '))
   puts(quoted[0][1].join(' '))
#}}}
end
===============================================================================



cheers.

-a
--
===============================================================================
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| A flower falls, even though we love it;
| and a weed grows, even though we do not love it. 
|   --Dogen
===============================================================================