Clean user input from CGI for output to WIN32::ODBC

[Max Harvey]

Hi,

I have a simple script which takes input from CGI, and outputs it to a
database with WIN32::ODBC.

The problem is when I encounter input with a single quote in it, it
messes up the SQL statement for the Win32::ODBC module.

How can I clean/phrase the input ($comments), so that it can be used
as part of my SQL statement?


Any help would be great... the script basically does what it is
supposed to do, but it can be crashed by the user putting in the wrong
input, something which I know is bad, just aren't good enough with
Perl yet to fix :(



Max.

[Matija Papec]

X-Ftn-To: Max Harvey

[email]google@DELTAL.org[/email] (Max Harvey) wrote:

>The problem is when I encounter input with a single quote in it, it
>messes up the SQL statement for the Win32::ODBC module.
>
>How can I clean/phrase the input ($comments), so that it can be used
>as part of my SQL statement?
>
>Any help would be great... the script basically does what it is
>supposed to do, but it can be crashed by the user putting in the wrong
>input, something which I know is bad, just aren't good enough with
>Perl yet to fix :(

perldoc DBI,
$sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");

or you can manually backslash your values.


--
Matija

[Ron Reidy]

Look qt quote() in perldoc DBI

Max Harvey wrote:

> Hi,
>
> I have a simple script which takes input from CGI, and outputs it to a
> database with WIN32::ODBC.
>
> The problem is when I encounter input with a single quote in it, it
> messes up the SQL statement for the Win32::ODBC module.
>
> How can I clean/phrase the input ($comments), so that it can be used
> as part of my SQL statement?
>
>
> Any help would be great... the script basically does what it is
> supposed to do, but it can be crashed by the user putting in the wrong
> input, something which I know is bad, just aren't good enough with
> Perl yet to fix :(
>
>
>
> Max.

--
Ron Reidy
Oracle DBA

[James Willmore]

On Sun, 14 Sep 2003 08:45:11 -0600
Ron Reidy <r_reidy@comcast.net> wrote:

> Look qt quote() in perldoc DBI
>
> Max Harvey wrote:

> > Hi,
> >
> > I have a simple script which takes input from CGI, and outputs it
> > to a database with WIN32::ODBC.

^^^^^^^^^^^^

> >
> > The problem is when I encounter input with a single quote in it,
> > it messes up the SQL statement for the Win32::ODBC module.
> >
> > How can I clean/phrase the input ($comments), so that it can be
> > used as part of my SQL statement?
> >
> >
> > Any help would be great... the script basically does what it is
> > supposed to do, but it can be crashed by the user putting in the
> > wrong input, something which I know is bad, just aren't good
> > enough with Perl yet to fix :(

Actually, the OP needs to use WIN32::DBIODBC, then he can look up the
'quote' method. There is no 'quote' method in WIN32::ODBC, I think.
Or, simply switch to DBI.

Just an observation :)

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. [url]http://www.gnu.org/licenses/gpl.txt[/url]
for more information.

a fortune quote ...
Idaho state law makes it illegal for a man to give his sweetheart
a box of candy weighing less than fifty pounds.

[Max Harvey]

James Willmore <jwillmore@cyberia.com> wrote in message news:<20030914164204.768ea317.jwillmore@cyberia.co m>...

> On Sun, 14 Sep 2003 08:45:11 -0600
> Ron Reidy <r_reidy@comcast.net> wrote:

> > Look qt quote() in perldoc DBI
> >
> > Max Harvey wrote:

> > > I have a simple script which takes input from CGI, and outputs it
> > > to a database with WIN32::ODBC.

> ^^^^^^^^^^^^

> > >
> > > The problem is when I encounter input with a single quote in it,
> > > it messes up the SQL statement for the Win32::ODBC module.
> > >
> > > How can I clean/phrase the input ($comments), so that it can be
> > > used as part of my SQL statement?

Thaks for everybodys help.

One of the early responces was to use my $sth = $dbh->prepare(sql);

This is actually DBI ot Win32::ODBC as far as I can tell.

Anyhow, the CGI script I was creating wasn't that large, so switching
from Win32::ODBC to DBI and DBD::ODBC wasn't to much work.

I have now done as advised/suggested, and I haven't managed to find
any combination of user input which will kill my script.

I have tried no input (underrun), lots and lots of input (overrunn),
weird not standard charests etc., and the script seems to hold its
own.

Also I guess now I am usng DBI, a change of backend won't be to
difficult if the script starts getting more work that it was expected
to.


Once again, thanks for all those who assisted.


Max.