Re: Simple question on SQL Injection
by jhourcle (Prior) on Oct 09, 2007 at 16:19 UTC
|
No need to test, just use placeholders.
Okay, technically, this only prevents it from being real problem, and it won't allow you pass to some alternate logic (eg, blacklist the incoming IP) when you see an injection attempt. The problem is that detecting abuse it like trying to detect spam e-mail -- there are some that are obvious, and some that might be acceptable, but you need to know the context. For instance, it's much easier to locate bad values in a numeric field, or if there should have only been a limited set of choices to validate against. If it's freeform text (eg, passwords), or even worse -- binary data into a blob (eg, an image file), you might not be able to validate it simply on what characters are present, but have to look for patterns, and even then, you might have false positives.
| [reply] |
|
Ah...I tried place holders but that did not work for me..
I had to use DataObject to use GetTbl etc as thats the only way I can get to the database.
Is there anyother way I can use place holders if my D/B access look something like this:
use DataObject;
my $table = DataObject->GetTbl("table");
my $query = <<_QUERY_;
select * from table where column is xyz
_QUERY_
DataObject->SetDBIdent("MYDB");
my $results = DataObject->DoSql($query);
Thanks
| [reply] |
|
You can't vary the table name with placeholders, nor field names. They only work with values. However, if you're consistent in your naming, and only use a limited set of characters, you can test to see if input is safe, even if not valid. (for instance, only letters, digits, and underscores)
warn "Invalid value" if ( $input =~ m/\W/ );
note -- '\W' matches any character not matched by '\w', which matches letters, numbers, and underscore. The list of what qualifies as a 'letter' is dependant upon your locale settings. If you wanted only ascii letters, use the following:
warn "Invalid value" if ( $input =~ m/[^a-zA-Z\d_]/ );
| [reply] [d/l] [select] |
|
| [reply] |
|
|
Re: Simple question on SQL Injection
by mwah (Hermit) on Oct 09, 2007 at 16:31 UTC
|
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);
...
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 | [reply] [d/l] |
|
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.
| [reply] |
|
| [reply] |
|
Thanks mwa,
But I cant use DBI, I can only use DataObject..is there anyway I can use palce holders with DataObject?
Thanks
| [reply] |
|
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] |
|
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
Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!
| [reply] [d/l] [select] |
Re: Simple question on SQL Injection
by ikegami (Patriarch) on Oct 09, 2007 at 16:09 UTC
|
Use replaceable parameters (avoiding the need for validation and quoting). | [reply] |
Re: Simple question on SQL Injection
by andreas1234567 (Vicar) on Oct 10, 2007 at 07:47 UTC
|
| [reply] |