thr3ads.net - Rails - Handling quotes inside of text submissions ... [Sep 2006]

If this information is useful, please help other people find it:
Share via:

David Johnson

2006-Sep-18 02:18 UTC

Handling quotes inside of text submissions ...

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



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Jeremy McAnally

2006-Sep-18 03:09 UTC

head link

Re: Handling quotes inside of text submissions ...

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

On 9/17/06, David Johnson <johnson_d-j9pdmedNgrk@public.gmane.org>
wrote:>
> 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
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

johnson_d-j9pdmedNgrk@public.gmane.org

2006-Sep-18 11:04 UTC

head link

Re: Handling quotes inside of text submissions ...

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 = 215

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 = 215


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Rails - Sep 2006 - Handling quotes inside of text submissions ...

Handling quotes inside of text submissions ...

Re: Handling quotes inside of text submissions ...

Re: Handling quotes inside of text submissions ...