Re: Simple question on SQL Injection

radix: how can I write a validation statement to detect SQL injection

1) the canonical way to prevent SQL injection is, like ikegami and
jhourcle have pointed out already, to use placeholders (they are
even "native" in mysql-5).

2) you can use the the "quote" method from DBI, like:

 ...
 $dbh = DBI->connect(...);
 $sql = sprintf "SELECT name FROM users WHERE name=%s AND passwd=%s",
           $dbh->quote($bad_name), 
           $dbh->quote($bad_pass);

 ...
[download]

For detection, you could just count the occurences of the \' char somewhere
in the input (or whatever quote the DB uses). That would be almost the only
danger (IIRC)

Regards

mwa

Comment on Re: Simple question on SQL Injection Download Code

Replies are listed 'Best First'.
Re^2: Simple question on SQL Injection by andreas1234567 (Vicar) on Oct 09, 2007 at 18:14 UTC
The canonical way to prevent SQL injection is to use placeholders. An alternative approach is to allow access to data through stored procedures only, and disallow dynamic sql altogether. Such an approach has a number of interesting benefits, including performance and security. -- Andreas	[reply]
Re^2: Simple question on SQL Injection by captHij (Initiate) on Oct 10, 2007 at 13:23 UTC
Sorry to hijack the thread, but I have been reading this one with interest. I am using DBI and mysql and want to use a blob to store binary files. I cannot find much information about inserts, blobs, and sql injection. Is it okay to use placeholders with blobs? Will it all "just work?" Do I have to do anything else? Will Britney be okay? I gotta know! Thanks in advance and sorry to carry on...	[reply]
Re^2: Simple question on SQL Injection by radix (Initiate) on Oct 09, 2007 at 16:34 UTC
Thanks mwa, But I cant use DBI, I can only use DataObject..is there anyway I can use palce holders with DataObject? Thanks	[reply]
Re^3: Simple question on SQL Injection by runrig (Abbot) on Oct 09, 2007 at 17:10 UTC
I don't see any DataObject module on CPAN (except for something under Spoon, but I don't think that's it). We can't help you with modules that we know nothing about. Is DBI installed? You might at least be able to call the quote() method from it on your parameters. Or see if your DataObject module can handle placeholders or has something like the quote() method.	[reply]
Re^3: Simple question on SQL Injection by naikonta (Curate) on Oct 10, 2007 at 19:11 UTC
Too bad you can't use DBI and I don't know what the heck DataObject is, but I hope it uses DBI underneath. Using `-T` switch could help in conjunction with `Taint` option of DBI (if, again, you use DBI). Use regex to validate user inputs, but you need to define what constitutes bad or good input. For example, to allow only alphanumeric character, `my $user = get_username(); if ($user =~ /^(\w+)$/) { $user = $1; } else { die "Hey, you gave me bad input: $user\n"; } # proceed with untainted $user` [download] Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!	[reply] [d/l] [select]