Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re^2: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite

by talexb (Canon)
on Jan 09, 2008 at 19:20 UTC ( #661466=note: print w/ replies, xml ) Need Help??


in reply to Re: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite
in thread Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite

    Nice work (although the outcome is not unexpected ;).

Thanks -- I was hoping to see the result that I did actually see, but I was very worried. I've always assumed that placeholders are safe. With the uncertainty, I did what any engineer or scientist would do -- I conducted an experiment to find out what was really happing.

The modules like DBIxC and Rose sound nice, and I will try DBIxC again -- ten years ago I was putting raw HTML into my Perl CGIs, and that's a no-no for me now. Perhaps in another ten years there won't be any SQL in my modules because I'm using DBIxC. Or maybe I'll reach that state of Nirvana sooner. I'll see.

    But if you really stick to plain DBI with placeholders you don't have to worry very much about SQL injection.

Yeah. :)

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds


Comment on Re^2: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite
Re^3: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite (boggle)
by tye (Cardinal) on Jan 09, 2008 at 20:17 UTC
    but I was very worried

    About a single quote character causing a problem? Wow, you must think that the DBD modules are almost completely untested?

    Though I don't see how you jump from preventing one trivial SQL injection attack to the grand conclusion that all injection attacks will surely be prevented. But placeholders aren't exactly rocket surgery so I'd be rather surprised if somebody could find character strings that are not properly handled by a DBD (ignoring character encoding problems which are still quite a nuisance), much less allowing SQL to be injected.

    But I've certainly found plenty of problems with DBD placeholders. None of them were the type that would allow for SQL injection. Most of them were in various versions of DBD::ODBC and include such things as not being able to handle dates without jumping through extra hoops of complexity (or just not being able to use dates via placeholders at all) and more vague problems of queries just not matching properly until I replaced the placeholders with simple string templates using DBD's ->quote(). There were also some problems with DBD::mysql and quotes around numerical values in some situations.

    Update: Also consider that your audience's FUD may be related to PHP which, from what I've heard, does some rather unreliable "magic" trying to automatically deal with single quotes in data to/from databases without making a clear distinction between the quoted strings and the (unquoted) string values and that this "magic" doesn't work very well.

    - tye        

        About a single quote character causing a problem? Wow, you must think that the DBD modules are almost completely untested?

      No -- I'm confident that the DBD modules are tested against this specific challenge.

      Anyway, just to be sure, I expanded my script ..

      And I still got good results ..

      So it's not guaranteed that these three DBDs are 100% protected, but a few obvious tests show that I'm fairly confident that the three DBDs that I care about are OK.

      Alex / talexb / Toronto

      "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (4)
As of 2014-12-28 22:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (183 votes), past polls