Handling quotes inside of text submissions

Rails
1

Problem:

When text is submitted that has embedded quotes, the insert and update
statements fail.

Is the endemic in Rails, a “feature” of the version of rails I am using,
or a
quirk (bug) in the firebird drivers?

Environment:
Ruby 1.8.4
Rails 1.1.6
FireRuby 0.4.1-i586-linux
Firebird 1.5.3
Linux 2.6.16-gentoo-r7

Example:
The exact text that highlighted this issue for me is the response to a
multiple part quizzing question - the quotes are a proper part of the
response. My spelling error is not :o).

“Why were you teaching in teh temple?”

Log dump of failed transaction:

Processing QuestionController#update (for 127.0.0.1 at 2006-09-17
20:31:55)
[POST]
Session ID: d33f4dc885fcc568bb16f476a063bca3
Parameters:
{“iscorrect”=>[“off”, “on”, “off”], “audiofilename”=>[“”, “”, “”],
“commit”=>“Save”, “answerPoints”=>[“0”, “5”, “0”], “imagefilename”=>[“”,
“”, “”], “action”=>“update”, “id”=>“33”,
“question”=>{“explanation_presentation”=>{“textvalue”=>“”},
“tip_presentation”=>{“textvalue”=>“Acts
4:7”}, “points”=>“0”, “time_allowed”=>“30”,
“presentation”=>{“textvalue”=>“What
question did the Sanhedrin ask Peter and John?”},
“parent”=>{“name”=>“Acts
Lesson 2 Red Level”}}, “controller”=>“question”,
“presentationtext”=>[“"Why
were you teaching in teh temple?"”, “"By what power or what name did
you do
this?”, “Both answers are
correct.”], “answerId”=>[“110”, “111”, “112”], “parent_id”=>{“3”=>“”}}
[4;35;1mUser Load (0.008409) [0m [0mSELECT FIRST 1 * FROM users
WHERE
(users.id = 1) [0m
[4;36;1mQuestion Columns (0.007643) [0m [0;1m SELECT
r.rdb$field_name,
r.rdb$field_source, f.rdb$field_type, f.rdb$field_sub_type,
f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale,
COALESCE(r.rdb$default_source, f.rdb$default_source)
rdb$default_source,
COALESCE(r.rdb$null_flag, f.rdb$null_flag) rdb$null_flag
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name
WHERE r.rdb$relation_name = ‘QUESTIONS’
ORDER BY r.rdb$field_position
[0m
[4;35;1mQuestion Load (0.004930) [0m [0mSELECT FIRST 1 * FROM
questions
WHERE (questions.“ID” = ‘33’ ) [0m
[4;36;1mQuiz Columns (0.006105) [0m [0;1m SELECT r.rdb$field_name,
r.rdb$field_source, f.rdb$field_type, f.rdb$field_sub_type,
f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale,
COALESCE(r.rdb$default_source, f.rdb$default_source)
rdb$default_source,
COALESCE(r.rdb$null_flag, f.rdb$null_flag) rdb$null_flag
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name
WHERE r.rdb$relation_name = ‘QUIZZES’
ORDER BY r.rdb$field_position
[0m
[4;35;1mQuiz Load (0.005433) [0m [0mSELECT FIRST 1 * FROM quizzes
WHERE
(quizzes.“ID” = 3 ) [0m
[4;36;1mPresentations Columns (0.006095) [0m [0;1m SELECT
r.rdb$field_name, r.rdb$field_source, f.rdb$field_type,
f.rdb$field_sub_type,
f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale,
COALESCE(r.rdb$default_source, f.rdb$default_source)
rdb$default_source,
COALESCE(r.rdb$null_flag, f.rdb$null_flag) rdb$null_flag
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name
WHERE r.rdb$relation_name = ‘PRESENTATIONS’
ORDER BY r.rdb$field_position
[0m
[4;35;1mPresentations Load (0.010192) [0m [0mSELECT FIRST 1 * FROM
presentations WHERE (presentations.“ID” = 212 ) [0m
[4;36;1mPresentations Load (0.006519) [0m [0;1mSELECT FIRST 1 *
FROM
presentations WHERE (presentations.“ID” = 214 ) [0m
[4;35;1mPresentations Load (0.005407) [0m [0mSELECT FIRST 1 * FROM
presentations WHERE (presentations.“ID” = 213 ) [0m
[4;36;1mAnswers Columns (0.007009) [0m [0;1m SELECT
r.rdb$field_name,
r.rdb$field_source, f.rdb$field_type, f.rdb$field_sub_type,
f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale,
COALESCE(r.rdb$default_source, f.rdb$default_source)
rdb$default_source,
COALESCE(r.rdb$null_flag, f.rdb$null_flag) rdb$null_flag
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name
WHERE r.rdb$relation_name = ‘ANSWERS’
ORDER BY r.rdb$field_position
[0m
[4;35;1mAnswers Load (0.006408) [0m [0mSELECT FIRST 1 * FROM
answers
WHERE (answers.“ID” = ‘110’ ) [0m
[4;36;1mPresentations Load (0.005998) [0m [0;1mSELECT FIRST 1 *
FROM
presentations WHERE (presentations.“ID” = 215 ) [0m
[4;35;1mAnswers Load (0.010111) [0m [0mSELECT FIRST 1 * FROM
answers
WHERE (answers.“ID” = ‘111’ ) [0m
[4;36;1mPresentations Load (0.006531) [0m [0;1mSELECT FIRST 1 *
FROM
presentations WHERE (presentations.“ID” = 216 ) [0m
[4;35;1mAnswers Load (0.007085) [0m [0mSELECT FIRST 1 * FROM
answers
WHERE (answers.“ID” = ‘112’ ) [0m
[4;36;1mPresentations Load (0.008240) [0m [0;1mSELECT FIRST 1 *
FROM
presentations WHERE (presentations.“ID” = 217 ) [0m
[4;35;1mPresentations Update (0.008904) [0m [0mUPDATE
presentations
SET “AUDIO” = NULL, “TEXTVALUE” = ‘What question did the Sanhedrin ask
Peter
and John?’, “VISUAL” = NULL WHERE id = 212 [0m
[4;36;1mPresentations Update (0.002931) [0m [0;1mUPDATE
presentations
SET “AUDIO” = NULL, “TEXTVALUE” = ‘’, “VISUAL” = NULL WHERE id = 213 [0m
[4;35;1mPresentations Update (0.003497) [0m [0mUPDATE
presentations
SET “AUDIO” = NULL, “TEXTVALUE” = ‘Acts 4:7’, “VISUAL” = NULL WHERE id =
214
[0m
[4;36;1mQuestion Update (0.003307) [0m [0;1mUPDATE questions
SET “TIP_PRESENTATION_ID” = 214, “MULTI_SELECT”
= ‘N’, “EXPLANATION_PRESENTATION_ID” = 213, “TIME_ALLOWED” = 30, “SEQ” =
8, “POINTS” = 0, “PRESENTATION_ID” = 212, “PARENT_ID” = 3 WHERE id = 33
[0m
[4;35;1mPresentations Update (0.003247) [0m [0mUPDATE
presentations
SET “AUDIO” = ‘’, “TEXTVALUE” = ‘“Why were you teaching in teh
temple?”’, “VISUAL” = ‘’ WHERE id = 215 [0m
[4;36;1mAnswers Update (0.004418) [0m [0;1mUPDATE answers SET
“SEQ” =
1, “POINTS” = 0, “PRESENTATION_ID” = 215, “ISCORRECT” = ‘N’, “PARENT_ID”
= 33
WHERE id = 110 [0m
[4;35;1mPresentations Update (0.002978) [0m [0mUPDATE
presentations
SET “AUDIO” = ‘’, “TEXTVALUE” = ‘"By what power or what name did you do
this?’, “VISUAL” = ‘’ WHERE id = 216 [0m
[4;36;1mAnswers Update (0.003279) [0m [0;1mUPDATE answers SET
“SEQ” =
2, “POINTS” = 5, “PRESENTATION_ID” = 216, “ISCORRECT” = ‘Y’, “PARENT_ID”
= 33
WHERE id = 111 [0m
[4;35;1mPresentations Update (0.003111) [0m [0mUPDATE
presentations
SET “AUDIO” = ‘’, “TEXTVALUE” = ‘Both answers are correct.’, “VISUAL” =
‘’
WHERE id = 217 [0m
[4;36;1mAnswers Update (0.009735) [0m [0;1mUPDATE answers SET
“SEQ” =
3, “POINTS” = 0, “PRESENTATION_ID” = 217, “ISCORRECT” = ‘N’, “PARENT_ID”
= 33
WHERE id = 112 [0m
Redirected to http://localhost:3000/question/list/33?parent_id=3
Completed in 0.44455 (2 reqs/sec) | DB: 0.15752 (35%) | 302 Found
[http://localhost/question/update/33]
QuestionController: missing default helper path question_helper

2

Are you using string interpolation or placeholders?

I mean…are you doing this:
sql = “INSERT INTO whatever VALUES(#{myvalue},#{another});”

or are you doing this…
sql = {“INSERT INTO whatever VALUES(?, ?)”, myvalue, another}

You should be doing the latter, because ActiveRecord will
automatically properly escape everything.

–Jeremy

3

My pertinent code:

    for a in answer do
      a.presentation.save!
      a.save!
    end

Rails Generated SQL (from log snip, unaltered code)

UPDATE presentations SET “AUDIO” = ‘’, “TEXTVALUE” = ‘“Why were you
teaching in teh temple?”’, “VISUAL” = ‘’ WHERE id = 215e

I have a workaround - I turn the double quotes into ". However,
it would be interesting to know where this quirk arises from.

Rails Generated SQL (altered code with workaround)

UPDATE presentations SET “AUDIO” = ‘’, “TEXTVALUE” = ‘“Why were
you teaching in teh temple?”’, “VISUAL” = ‘’ WHERE id = 215e