TEXT Type Question

am 07.10.2003 09:41:45 von Steve Schein

All:

I am converting sections of web pages to strings and storing them in a
MYSQL database. The field I use for storage has been defined with a
TEXT type and works fine for most entries with the exception of things
that look like this:

"Rants & Raves Readers on masked monopolies ... RIAA dorm-room
searches ... Maj. Gen. Monty Python ... and more.

Furthermore Smoking? What Smoking? In a voluntary, anonymous survey, 38
percent of cancer patients said they believe that cancer spreads
when exposed to air during surgery. The questionnaire was given to 626
patients at five urban clinics specializing in lung tumors and
diseases. "It may be surprising for some people to hear about this,
but it's not surprising to me or for many doctors who confront
patients with (cancer)," said Dr. Mitchell Margolis, the lead
researcher. Margolis said he decided to conduct the survey after
hearing the myth repeated by a "disconcerting number" of patients. Of
those who believed the myth, 24 percent said they would reject lung
cancer surgery based on that belief."

I get the following error message:

>>>DBD::mysql::db do failed: You have an error in your SQL syntax.
Check the manual that corresponds to your MySQL server version for the
right syntax to use near 'It may be surprising for some people to hear
about this, but at My_module.pm line 292.

For the moment I'm assuming this has something to do with the quotation
marks beginning at "It may be surprising...".

Is that true?

If so, how might I deal with this?

If not, what else should I look for?

TIA,

Steve

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: TEXT Type Question

am 07.10.2003 22:36:31 von brianm

When your inserting text that might possibly contain quotes you can use
$dbh->quote($value);

for example:
$sql = sprintf "INSERT foo (text_column) VALUES (%s)", $dbh->quote($mystring);

Brian

> All:
>
> I am converting sections of web pages to strings and storing them in a
> MYSQL database. The field I use for storage has been defined with a
> TEXT type and works fine for most entries with the exception of things
> that look like this:
>
> "Rants & Raves Readers on masked monopolies ... RIAA dorm-room
> searches ... Maj. Gen. Monty Python ... and more.
>
> Furthermore Smoking? What Smoking? In a voluntary, anonymous survey, 38
> percent of cancer patients said they believe that cancer spreads
> when exposed to air during surgery. The questionnaire was given to 626
> patients at five urban clinics specializing in lung tumors and
> diseases. "It may be surprising for some people to hear about this,
> but it's not surprising to me or for many doctors who confront
> patients with (cancer)," said Dr. Mitchell Margolis, the lead
> researcher. Margolis said he decided to conduct the survey after
> hearing the myth repeated by a "disconcerting number" of patients. Of
> those who believed the myth, 24 percent said they would reject lung
> cancer surgery based on that belief."
>
> I get the following error message:
>
> >>>DBD::mysql::db do failed: You have an error in your SQL syntax.
> Check the manual that corresponds to your MySQL server version for the
> right syntax to use near 'It may be surprising for some people to hear
> about this, but at My_module.pm line 292.
>
> For the moment I'm assuming this has something to do with the quotation
> marks beginning at "It may be surprising...".
>
> Is that true?
>
> If so, how might I deal with this?
>
> If not, what else should I look for?
>
> TIA,
>
> Steve
>
>
>
>
>
>
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=brianm@invite.net

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: TEXT Type Question

am 07.10.2003 22:36:31 von brianm

Re: [OT] TEXT Type Question

am 07.10.2003 22:42:18 von Leif W

One way is to do some data scrubbing (i.e. assume all web input is dirty and
tainted and must be scrubbed so that malicious users can't break out of the
SQL statement or something, i.e. if any of this is to be automated).

For example, quote every single quote twice. ' becomes ''. For every
double quote, maybe just change it to the proper HTML entity, ".
Otherwise, try doublequoting the double quotes as well (I think, not sure),
" becomes "", or maybe escaping (again, not sure), so " becomes \".

Hope that helps,

Leif

----- Original Message -----
From: "Steve Schein"
To:
Sent: Tuesday, October 07, 2003 3:41 AM
Subject: TEXT Type Question

> All:
>
> I am converting sections of web pages to strings and storing them in a
> MYSQL database. The field I use for storage has been defined with a
> TEXT type and works fine for most entries with the exception of things
> that look like this:
>
> "Rants & Raves Readers on masked monopolies ... RIAA dorm-room
> searches ... Maj. Gen. Monty Python ... and more.
>
> Furthermore Smoking? What Smoking? In a voluntary, anonymous survey, 38
> percent of cancer patients said they believe that cancer spreads
> when exposed to air during surgery. The questionnaire was given to 626
> patients at five urban clinics specializing in lung tumors and
> diseases. "It may be surprising for some people to hear about this,
> but it's not surprising to me or for many doctors who confront
> patients with (cancer)," said Dr. Mitchell Margolis, the lead
> researcher. Margolis said he decided to conduct the survey after
> hearing the myth repeated by a "disconcerting number" of patients. Of
> those who believed the myth, 24 percent said they would reject lung
> cancer surgery based on that belief."
>
> I get the following error message:
>
> >>>DBD::mysql::db do failed: You have an error in your SQL syntax.
> Check the manual that corresponds to your MySQL server version for the
> right syntax to use near 'It may be surprising for some people to hear
> about this, but at My_module.pm line 292.
>
> For the moment I'm assuming this has something to do with the quotation
> marks beginning at "It may be surprising...".
>
> Is that true?
>
> If so, how might I deal with this?
>
> If not, what else should I look for?
>
> TIA,
>
> Steve
>
>
>
>
>
>
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=warp-9.9@usa.net
>
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: [OT] TEXT Type Question

am 07.10.2003 22:42:18 von Leif W