Handling quotes inside of text submissions ...

rubyonrails-talk
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

<b>Rails Generated SQL (from log snip, unaltered code)</b>

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 &quot;. However, it would be interesting to know where this quirk arises from.

<b>Rails Generated SQL (altered code with workaround)</b>

UPDATE presentations SET "AUDIO" = '', "TEXTVALUE" = '&quot;Why were you teaching in teh temple?&quot;', "VISUAL" = '' WHERE id = 215e