Can't pass email address in SQL select statement

davart has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Can't pass email address in SQL select statement by hardburn (Abbot) on Oct 20, 2003 at 15:04 UTC
Don't pass the e-mail address directly into the SQL statement. Use placeholders instead and pass the address to `execute()`: `my $sql = q(select username from user where address = ?); my $sth1 = $dbh->prepare($sql); $sth1->execute($address);` [download] ---- I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident. -- Schemer `:(){ :\|:&};:` Note: All code is untested, unless otherwise stated	[reply] [d/l] [select]
Re: Re: Can't pass email address in SQL select statement by davart (Initiate) on Oct 20, 2003 at 16:32 UTC
Thanks! It works great.	[reply]
Re: Can't pass email address in SQL select statement by Abigail-II (Bishop) on Oct 20, 2003 at 15:06 UTC
Yup, because `select username from user where address=foo@ebi-llc.com` [download] isn't valid SQL. Use a place holder: `my $sql = qq(select username from user where address=?); my $sth1 = $dbh->prepare($sql); $sth1 -> execute ($address);` [download] Abigail	[reply] [d/l] [select]
Re: Can't pass email address in SQL select statement by Limbic~Region (Chancellor) on Oct 20, 2003 at 15:05 UTC
davart, Try it using placeholders. `my $sth1 = $dbh->prepare("select username from user where address=?"); $sth1->execute($address);` [download] Using placeholders can avoid all kinds of pitfalls. Cheers - L~R	[reply] [d/l]
Re: Can't pass email address in SQL select statement by virtualsue (Vicar) on Oct 20, 2003 at 15:08 UTC
Try using DBI::quote to escape those pesky characters.	[reply]
Re: Can't pass email address in SQL select statement by gnangia (Scribe) on Oct 20, 2003 at 15:39 UTC
Most likely your address field is of type varchar which means you will need single quotes around $address - my $sql = ("select username from user where address='$address')";	[reply]
Re: Re: Can't pass email address in SQL select statement by hardburn (Abbot) on Oct 20, 2003 at 16:37 UTC
If `$address`, presumably coming from user input, should happen to contain `'; DELETE FROM username WHERE id > -1; '`, what do you do? The correct solution is to use placeholders (or if you feel like typing more code, `DBI::quote`), as the other posters pointed out. ---- I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident. -- Schemer `:(){ :\|:&};:` Note: All code is untested, unless otherwise stated	[reply] [d/l] [select]
Re: Re: Re: Can't pass email address in SQL select statement by CountZero (Bishop) on Oct 20, 2003 at 18:58 UTC
And use Taint-mode to guard against dangerous user input. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]