Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: Simple question on SQL Injection

by mwah (Hermit)
on Oct 09, 2007 at 16:31 UTC ( #643737=note: print w/replies, xml ) Need Help??


in reply to Simple question on SQL Injection

radixhow 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

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
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...

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
      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.
      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!

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://643737]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2019-09-15 13:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    The room is dark, and your next move is ...












    Results (180 votes). Check out past polls.

    Notices?