Quotes in Query Statement

am 11.09.2003 05:38:44 von Mike Garner

I'm having problems updating a text field in a MySQL (4.0.12) database. I'm
taking user input from a textarea field on an HTML form and attempting to
update a Text field within the database. The CGI script works fine with
regular text but when special characters are entered I get a Server 500
error on the post operation. Here's the PERL snippet that builds the query.

my $query="Update formtable
Set textarea1='$formvalue'
Where formid=1";

When I was running the query like the above it really didn't like the ' and
" characters (at least those are the two I found, there may be more) when
entered in the textarea so I modified the query statement to look like this:

my $query=qq(
Update formtable
Set textarea1=\"$formvalue\"
Where formid=1
);

This fixed the problem for ticks and single-quotes but not the
double-quote. If a " is entered, the CGI errors. How do I best deal with
this type of scenario (hopefully without searching for and replacing these
characters before the query $formvalue=~s/\"//g; ) I guess what I'm asking
is how to I properly escape the variable to no matter what the user enters,
it gets passed straight thru to the text field in the database without error.

Any advice is greatly appreciated.

~Mike

------------------------------------------------------------ --
Mike Garner
Western State College
Email: mgarner@western.edu
Voice: 970-943-3123
Fax: 970-943-7069

--
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: Quotes in Query Statement

am 11.09.2003 06:33:53 von Dodger

Use placeholders, like so:

my $statement = <<"EOF";
UPDATE formtable
SET textarea1 = ?
WHERE formid = 1
EOF

my $query = $dbh->prepare($statement);

$query->execute($formvalue);

Each argument given to the execute() method will fill in one of the ?s you
used and will properly excape and quote it.
Do not put the ? inside quotes (single or double) or it will be ignored.

Placeholders are also called 'bind variables' and 'hooks' depending on who
you talk to.

--
Dodger

----- Original Message -----
From: "Mike Garner"
To:
Sent: Wednesday, September 10, 2003 8:38 PM
Subject: Quotes in Query Statement

> I'm having problems updating a text field in a MySQL (4.0.12) database.
I'm
> taking user input from a textarea field on an HTML form and attempting to
> update a Text field within the database. The CGI script works fine with
> regular text but when special characters are entered I get a Server 500
> error on the post operation. Here's the PERL snippet that builds the
query.
>
> my $query="Update formtable
> Set textarea1='$formvalue'
> Where formid=1";
>
> When I was running the query like the above it really didn't like the '
and
> " characters (at least those are the two I found, there may be more) when
> entered in the textarea so I modified the query statement to look like
this:
>
> my $query=qq(
> Update formtable
> Set textarea1=\"$formvalue\"
> Where formid=1
> );
>
> This fixed the problem for ticks and single-quotes but not the
> double-quote. If a " is entered, the CGI errors. How do I best deal with
> this type of scenario (hopefully without searching for and replacing these
> characters before the query $formvalue=~s/\"//g; ) I guess what I'm asking
> is how to I properly escape the variable to no matter what the user
enters,
> it gets passed straight thru to the text field in the database without
error.
>
> Any advice is greatly appreciated.
>
> ~Mike
>
>
>
> ------------------------------------------------------------ --
> Mike Garner
> Western State College
> Email: mgarner@western.edu
> Voice: 970-943-3123
> Fax: 970-943-7069
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=sean@aquest.com
>
>

--
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: Quotes in Query Statement

am 11.09.2003 06:33:53 von Dodger

Re: Quotes in Query Statement

am 11.09.2003 10:46:31 von Ulrich Borchers

Assuming you are using DBI and have a databse handle like
$handle =3D DBI->connect(..)
then use
$formvalue =3D $handle->quote($formvalue)

Note that this escapes all special characters
that might screw up you query AND puts
quotes around the string. Therefore
don't use any more quotes in your query
after $handle->quote'ing

$query =3D "UPDATE table SET textarea1=3D$formvalue";

Uli

On 10 Sep 2003 at 21:38, Mike Garner wrote:

> I'm having problems updating a text field in a MySQL (4.0.12) database. =
I'm
> taking user input from a textarea field on an HTML form and attempting t=
o
> update a Text field within the database. The CGI script works fine with=

> regular text but when special characters are entered I get a Server 500
> error on the post operation. Here's the PERL snippet that builds the qu=
ery.
>
> my $query=3D"Update formtable
> Set textarea1=3D'$formvalue'
> Where formid=3D1";
>
> When I was running the query like the above it really didn't like the ' =
and
> " characters (at least those are the two I found, there may be more) whe=
n
> entered in the textarea so I modified the query statement to look like t=
his:
>
> my $query=3Dqq(
> Update formtable
> Set textarea1=3D\"$formvalue\"
> Where formid=3D1
> );
>
> This fixed the problem for ticks and single-quotes but not the
> double-quote. If a " is entered, the CGI errors. How do I best deal wit=
h
> this type of scenario (hopefully without searching for and replacing the=
se
> characters before the query $formvalue=3D~s/\"//g; ) I guess what I'm as=
king
> is how to I properly escape the variable to no matter what the user ente=
rs,
> it gets passed straight thru to the text field in the database without e=
rror.
>
> Any advice is greatly appreciated.
>
> ~Mike
>
>
>
> ------------------------------------------------------------ --
> Mike Garner
> Western State College
> Email: mgarner@western.edu
> Voice: 970-943-3123
> Fax: 970-943-7069
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dubo1@gmx.de
>

--
Dipl.-Inf. Ulrich Borchers
MEGABIT Informationstechnik GmbH
Karstr. 25, 41068 Mönchengladbach
Tel. +49 2161 30898-0

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

Re: Quotes in Query Statement

am 11.09.2003 10:46:31 von Ulrich Borchers