From: Markus Fischer <markus@...>
Date: 2011-03-28T06:14:10+09:00
Subject: Even more 1.9 encoding issues (was: Re: SQLite3/Sinatra not returning results)

On 29.11.2010 12:39, Brian Candler wrote:
> Ergh. I can replicate this, with ruby 1.9.2p0 (rvm) + sqlite3-ruby-1.3.2 
> (gem) under Ubuntu Lucid x86_64.
> 
> ruby-1.9.2-p0 > require 'sqlite3'
>  => true
> ruby-1.9.2-p0 > db = SQLite3::Database.new("foo.db")
>  => #<SQLite3::Database:0x00000002631bc0>
> ruby-1.9.2-p0 > db.execute("create table foo (id integer auto_increment, 
> bar varchar(255))")
>  => []
> ruby-1.9.2-p0 > db.execute("insert into foo (bar) values ('hello')")
>  => []
> ruby-1.9.2-p0 > s1, s2, s3 = "hello", 
> "hello".force_encoding("US-ASCII"), "hello".force_encoding("ASCII-8BIT")
>  => ["hello", "hello", "hello"]
> ruby-1.9.2-p0 > s1 == s2
>  => true
> ruby-1.9.2-p0 > s1 == s3
>  => true
> ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s1)
>  => [[nil, "hello"]]
> ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s2)
>  => [[nil, "hello"]]
> ruby-1.9.2-p0 > db.execute("select * from foo where bar=?", s3)
>  => []
> 
> So s1 == s3, but the two queries give different results?? This is 
> barking mad. Having said that, I can't find anything in the sqlite3-ruby 
> documentation which says how queries may be affected by encodings, so 
> the behaviour is undefined.

I was just affected by that, Brian, rhanks for analyzing this problem in
the past! I'm running 1.9.2-p180 in my case. Now when I re-read the
other thread "A question about Ruby 1.9's "external encoding" when
Robert concludes "The rule as such is pretty clear IMHO" and "Now,
everything is clear" I feel like:

nothing.is.clear.to.me

What is this whole character encoding madness about? I never encountered
a language I stumbled into so many subtle problems with encodings left
and right (mainly working with Java in my case). The more I work with
ruby 1.9, the more confusing it gets. I'm getting worried. What am I
missing what others do know that I run into such troubles?

Getting back to this specific case, where's the problem exactly here? Is
it Sinatra? My source file? The sqlite database?

I wrote a simple testing app for the sqlite db and got this:

$ sqlite3 database.sqlite3 .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user ( username varchar(255), password varchar(255), email
varchar(255));
INSERT INTO "user" VALUES('a','b','c');
COMMIT;


$ cat dbitest.rb
require 'dbi'
require 'pp'

dbi = DBI.connect('dbi:SQLite3:database.sqlite3')

sql = "SELECT * FROM user WHERE username = ? AND password = ?"

username = 'a'
password = 'b'

pp username.encoding, password.encoding, dbi.select_one(sql, username,
password)[0].encoding


$ ruby dbitest.rb
#<Encoding:US-ASCII>
#<Encoding:US-ASCII>
#<Encoding:UTF-8>


Within Sinatra it is as you said:

Relevant excerpt of the code:

 if @auth.provided? && @auth.basic? && @auth.credentials
     sql = "SELECT * FROM user WHERE username = ? AND password = ?"
     username = @auth.credentials[0]
     password = @auth.credentials[1]
     pp username.encoding, password.encoding, $dbi.select_one(sql,
username, password)
     username.force_encoding('US-ASCII')
     password.force_encoding('US-ASCII')
     pp username.encoding, password.encoding, $dbi.select_one(sql,
username, password)[0].encoding
 end


This is the output

#<Encoding:ASCII-8BIT>
#<Encoding:ASCII-8BIT>
nil
#<Encoding:US-ASCII>
#<Encoding:US-ASCII>
#<Encoding:UTF-8>

Stunning.

- Markus