Quoted string problem

ruby-talk
1

Hi,

I have a “”“database”"" of 11513 shortwave transmitters with their
frequency, on-air time and a lot of other informations.
These database is in a “flat ascii file”: Each broadcaster gets one
line of 294 charcters. Each “column” is only sperated from the others
by known offsets.

My little ruby program reads line by line (broadcaster by
broadcaster) from this file and excerpts the wanted informations by
expressions like “frequncy = line[22…27]”.

Then an sql-statement is formed and sent to mysqld, which inserts the
data into the database.

So far so nice…

BUT!

There is one filed called “comment” in the database, where ANYTHING
printable can be exspected.

One line says has a comment like

		   International service "A".

At this point, mysqld jumps from the rail and says “STOP! SYNTAX
VIOLATED”.

Generally: how can I process input to escape “special” characters
before I send them to mysqld?

(In perl there is a command called “quotemeta”, which does the
thing…I found nothing equivalent in the docs for ruby).

Kind regards and thank you very much for your help in advance.
Meino

2

There is one filed called “comment” in the database, where ANYTHING
printable can be exspected.

One line says has a comment like

  	   International service "A".

At this point, mysqld jumps from the rail and says “STOP! SYNTAX
VIOLATED”.

Generally: how can I process input to escape “special” characters
before I send them to mysqld?

With DBI it would be something like this:

@dbh.do(“insert into mydb (station,comment) values (?,?)”, stationid, comment)

i.e. use “?” as a placeholder and then bind a variable to it. There is also
a ‘quote’ method of the database handle you can use.

(In perl there is a command called “quotemeta”, which does the
thing…I found nothing equivalent in the docs for ruby).

This article talks about it:
http://www.kitebird.com/articles/ruby-dbi.html

See section entitled “Quoting, Placeholders, and Parameter Binding”

Regards,

Brian.

···

On Fri, Aug 29, 2003 at 11:28:28PM +0900, Meino Christian Cramer wrote:

3

Hi,

These database is in a “flat ascii file”: Each broadcaster gets one
line of 294 charcters. Each “column” is only sperated from the others
by known offsets.
[…]
Generally: how can I process input to escape “special” characters

Regexp.escape(aString)

before I send them to mysqld?

(In perl there is a command called “quotemeta”, which does the

Regexp.escape is also known as: Regexp.quote(aString)

    Hugh
···

On Fri, 29 Aug 2003, Meino Christian Cramer wrote:

4

Hi Hugh,

Thanks for your reply !

Hi,

These database is in a “flat ascii file”: Each broadcaster gets one
line of 294 charcters. Each “column” is only sperated from the others
by known offsets.
[…]
Generally: how can I process input to escape “special” characters

Regexp.escape(aString)

before I send them to mysqld?

(In perl there is a command called “quotemeta”, which does the

Regexp.escape is also known as: Regexp.quote(aString)

    Hugh

This is (nearly) exactly for what I have searched for…

Unfortunately the “'” is not escaped and this character is that,
what make my headache…

I have a broadcaster called “N’Djamena”.

The “'” in its name confuses the string sent to mySQL…

I tried (with irb) the following. But I cannot figure out, what
game ruby is playing with me… :wink:

(in irb:)

a=“N’Djamena”
=> “N’Djamena”
a
=> “N’Djamena”
a.gsub( “'”,“X”)
=> “NXDjamena”
a.gsub( “'”,“'”)
=> “N’Djamena”
a.gsub( “'”,“\'”)
=> “NDjamenaDjamena”

What is happening here. I exspected the last expression to be
evalutated to “N'Djamena”.

But I am a ruby newbie, so ruby is right and me is wrong…but why
and what ?

By the way: I tried DBI but it seems not 1.8.0-ready ? I got a lot
of errors…

Now I am using ruby-mysql-0.2.1.tar.gz, which works fine…
beside my “special-char” problems.

I am happy, if you could give me an additional tip/hint…

Thank you very much in advance !

Keep hacking!
Meino

···

From: Hugh Sasse Staff Elec Eng hgs@dmu.ac.uk
Subject: Re: Quoted string problem
Date: Sat, 30 Aug 2003 08:29:11 +0900

5

Generally: how can I process input to escape “special” characters

Regexp.escape(aString)

No, that’s the wrong escaper. If you’re sending SQL, then you need to use
SQL escaping rules, not Regexp escaping rules.

Unfortunately the “'” is not escaped and this character is that,
what make my headache…

I have a broadcaster called “N’Djamena”.

The “'” in its name confuses the string sent to mySQL…

Indeed.

By the way: I tried DBI but it seems not 1.8.0-ready ? I got a lot
of errors…

It works just fine for me. I get a couple of warnings only, no errors. I use
DBI-0.0.20 with mysql, OCI8, sqlite

Now I am using ruby-mysql-0.2.1.tar.gz, which works fine…
beside my “special-char” problems.

Ah, OK. I think this is the ‘pure ruby’ solution which talks the Mysql
protocol directly over the wire.

I use mysql-ruby-2.4.4, which wraps the C API. Probably this is what DBI
expects, which would explain why it doesn’t work for you.

mysql-ruby contains a class method: Mysql.escape_string (alias Mysql.quote)
which does exactly what you want.

Have you checked the documentation for ruby-mysql to see if it has such a
function? If not, maybe you should change.

Regards,

Brian.

···

On Sat, Aug 30, 2003 at 04:05:18PM +0900, Meino Christian Cramer wrote:

6

Hi Brian !

mysql-ruby contains a class method: Mysql.escape_string (alias Mysql.quote)
which does exactly what you want.

Have you checked the documentation for ruby-mysql to see if it has such a
function? If not, maybe you should change.

I FOUND IT !!! OH HAPPY DAY !!! ;)))

Thank you for directly pointing me to things which I already have
:O)))

I was too blind…

But now another probelm hits me:

The data for my (hopefully soon existing) shortwave broadcaster
database is read line by line (broadcaster by broadcaster) from a
“pure text” file.

On position 279 there is a single character, which reflects the
status of the broadcaster. It one character of [A-Z].

I did the following:

			status = line[279]

I got

			72

(“H” is the 72scnd character of the ASCII table.)

Oh, I thought…better to do a

			status = status.to_s()

Now I got a

			"72"

Damn! In the input file there is literally a “H”. How can I convince
Ruby to read a “H” and no “72” or 72?
Sometimes I think, the “principle of leas surprise” isn’t working
everywhere to its completeness… :wink:

Kind regards,
Meino

···

From: Brian Candler B.Candler@pobox.com
Subject: Re: Quoted string problem
Date: Sat, 30 Aug 2003 18:26:15 +0900

7

By the way: I tried DBI but it seems not 1.8.0-ready ? I got a lot
of errors…

It works just fine for me. I get a couple of warnings only, no errors. I use
DBI-0.0.20 with mysql, OCI8, sqlite

I, too, am running Ruby 1.8, DBI 0.0.20 (but with Postgresql) without
problems.

mysql-ruby contains a class method: Mysql.escape_string (alias Mysql.quote)
which does exactly what you want.

Meino mentions later that he found a quote method. I would just like to
remind folks that if you are using DBI, the quote method is part of the
driver and can be accessed via …

dbh = DBI.connect(“DBI:yada:yada”, “yada”, nil)
dbh.quote(“N’Djamena”) # => “‘N’‘Djamena’”

The DBI version /should/ take into account different quoting
requirements of the individual drivers (if it doesn’t, that’s a bug!).
Explicit quoting isn’t needed if you are using the “?” parameters (as
you should!).

···

On Sat, 2003-08-30 at 05:26, Brian Candler wrote:

–
– Jim Weirich jweirich@one.net http://onestepback.org

“Beware of bugs in the above code; I have only proved it correct,
not tried it.” – Donald Knuth (in a memo to Peter van Emde Boas)

8

        status = status.to_s()

use Integer#chr

svg% ruby -e 'p 72.chr'
"H"
svg%

Guy Decoux

9

I did the following:

  		status = line[279]

I got

  		72

(“H” is the 72scnd character of the ASCII table.)

Oh, I thought…better to do a

  		status = status.to_s()

Now I got a

  		"72"

Damn! In the input file there is literally a “H”. How can I convince
Ruby to read a “H” and no “72” or 72?

line[279…279]

Sometimes I think, the “principle of leas surprise” isn’t working
everywhere to its completeness… :wink:

I would agree with you. It’s just one of those things you get used to.

Regards,

Brian.

···

On Sat, Aug 30, 2003 at 10:18:18PM +0900, Meino Christian Cramer wrote: