Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Preventing SQL injection attacks: are -T and placeholders not enough?

by talexb (Canon)
on Jan 09, 2008 at 04:12 UTC ( #661249=perlquestion: print w/ replies, xml ) Need Help??
talexb has asked for the wisdom of the Perl Monks concerning the following question:

I was recently asked about how to prevent SQL injection attacks from CGI form variables. My answer was 'placeholders' -- I haven't written SQL without placeholders in about eight years. Placeholders are part of my standard toolbox.

Anything else? I was asked. Puzzled, I thought about it, then added #!/usr/bin/perl -Tw at the top of the white board, turning on taint-checking for the entire script. Anything more? Nope -- I had no more ideas.

You need to run the form data through a regex to sanitize it, I was told. Yes, someone else added -- some of the DBD modules don't do a very good job of 'quoting' the data values.

I admit I haven't spent a lot of time digging through the DBD modules to find out exactly how they do their job, but I've assumed that it was a safe enough practice to use just placeholders to prevent SQL injection attacks.

Comments? Thoughts? DBD modules that might be suspect?

Update: I've posted an update where I try to do an SQL injection into MySQL, PostgreSQL and SQLite database tables using placeholders. All three attempts fail.

Alex / talexb / Toronto

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

Comment on Preventing SQL injection attacks: are -T and placeholders not enough?
Download Code
Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by Juerd (Abbot) on Jan 09, 2008 at 04:22 UTC
    Can you contact these people who told you that some DBDs don't do a good job quoting values? They apparently have experience that you and I do not, and it'd be really helpful if they shared it.

      I'll probably be talking with them again later this week. At the time, I was a little surprised (OK, I was just about dumbfounded), but now I'm going to follow up to find out more.

      Alex / talexb / Toronto

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

        Any news, perhaps?
Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by starX (Chaplain) on Jan 09, 2008 at 04:41 UTC
    It's not to prevent injection attacks per se, but in an app I'm working on now, everything gets passed through a s/'//g to prevent any accidental SQL interpretation. That should take care of any single quotes, and coupled with using place holders, I can't think of how anything would get through.

    Then again, it's getting late and my imagination is a little tired :)

    Update: Maybe I should have clarified that it's a pretty specific environment where single quote characters shouldn't exist, per client instructions.

      Hopefully your inputs are not addresses, names, plain old text and such.
      Don't do that. Use placeholders. They handle this for you.
      If youre going to strip out apostrophes, then youd probably better also make sure that its set up so the users know they cant (or at least theyre not allowed to) use any contractions. While youre at it, dont forget to make it clear that any possessives in the users input will also be damaged.
Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by graff (Chancellor) on Jan 09, 2008 at 05:22 UTC
    ... I thought about it, then added #!/usr/bin/perl -Tw at the top of the white board, turning on taint-checking for the entire script. Anything more? Nope -- I had no more ideas.

    You need to run the form data through a regex to sanitize it, I was told...

    Erm, but... you would have understood already that by turning on taint checking, you need to do stuff like "run the form data through a regex", because that is what it takes to untaint the tainted data.

    So either the person was simply making a point that your reference to taint-checking was an incomplete answer (did you forget to say why it helps to add "-T" and what else needs to be done once you add it?), or else the person didn't really understand the concept of taint-checking (which means you really should have given a complete answer about it in the first place).

    And for someone else to add a comment about inadequate quoting in "some" DBD modules is kind of a non-sequitur, not directly related to taint checking. It would have been nice to have the presence of mind to say "which DBD modules are you thinking of, in particular, and did you observe specific cases?", but it's worthwhile to consider that if the "sanitizing" logic for passing the taint-check is not sufficiently careful, one can still face sql injections (or at least embarrassing errors) using "untainted" strings. Hence the need for placeholders in addition to taint-checking.

    And "passing strings through a regex" is too vague to qualify as a "solution"; it's generally better (when possible) to handle taint-checking with things like hash-key lookups or similar tests against trusted data. Expectations for CGI parameter values should be as specific as possible.

    (updated to fix a minor grammar glitch)

Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by perrin (Chancellor) on Jan 09, 2008 at 05:36 UTC
    There are certain cases where using placeholders is difficult (e.g. the column name or table name is being set dynamically), but I can't think of any case where you would use placeholders and they wouldn't properly quote your arguments. This sounds like some kind of posturing or misunderstanding on their part. I doubt there is anything to it.
Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by McDarren (Abbot) on Jan 09, 2008 at 07:27 UTC
    Just a note on placeholders...

    I've also been a staunch user (and advocate) of placeholders for quite some time. However, some time ago I found myself in a position where I had to make DB connections to a MS-SQL server from a Linux box. Those that have been down this path will understand when I say that this was quite a painful and frustrating experience.

    I eventually settled on a solution that uses a combination of DBD::Sybase and FreeTDS (mostly because this solution was the least painful to get up and working), but to my disappointment I discovered that placeholder support is lacking in this solution, which meant that we had to be very careful with the code.

    I've not looked into it for a while, but as far as I'm aware there is no easy workaround to this. But I'd be very pleased to discover that there is, if anyone is aware of one?

    Cheers,
    Darren

      Yes, it is unfortunate that FreeTDS doesn't support placeholders in their Client Library implementation - because they do support placeholders in their ODBC implementation.

      The FreeTDS team is quite active, so I'm hoping that this support will eventually be added. Of course I'm sure they'd be more than happy if someone had the time and the energy to lend a hand and try to add this functionality...

      Michael

Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by Win (Novice) on Jan 09, 2008 at 09:55 UTC
    I don't know what database system you are using. However, I would nearly always suggest holding your SQL within a stored procedure held within the database. The stored procedure will not execute unless the variables meet the data types set in the stored procedure. It has other potential added security benefits also.

    Incidentally, I have not heard about -T for taint checking. I'll do some research on that - sounds interesting.

    Update : I don't use taint checking because I don't use CGI. I have an interface layer that processed requests that come in, in the form of flat files. These flat files are checked for malicious looking requests. I place conditions that the requests have to meet and I actively look for malicious looking requests. Both positive and negative screening, if you like. That might be another approach.
      "Incidentally, I have never heard of using -T for taint checking. What is that about?"

      Firstly, I am not sure how many times I have told you this, if you are going to update a post, it is better indicate in the post what you have changed.

      So to answer your original question, taint checking is well documented, has been discussed in many articles, it is covered in relevant tutorials and super search returns many hits. So once again, reading the documentation should answer your question.

      Update: Adding Wins unedited post as suggested by bart:
      &#8722; <node id="661296" title="Re: Preventing SQL injection attacks: are + -T and placeholders not enough?" created="2008-01-09 04:55:06" updat +ed="2008-01-09 04:55:06"> <type id="11"> note</type> <author id="304479"> Win</author> &#8722; <data> &#8722; <field name="doctext"> I don't know what database system you are using. However, I would nea +rly always recommend holding your SQL within a stored procedure held +within the database. The stored procedure will not execute unless th +e variables meet the data types set in the stored procedure. It has +other added benefits potentially. <br><br> Incidentally, I have never heard of using -T for taint checking. What + is that about? </field> <field name="root_node"> 661249</field> <field name="parent_node"> 661249</field> </data> </node>
      Martin
      I don't use taint checking because I don't use CGI
      Tainted data comes from just every input in any environment your programs run but CGI is indeed a kind of wild one compared to locally run programs. However, even you could damage your own system with innocent-looking inputs to your non-CGI programs. Using -T depends on how much security consideration you (should) put on your programs. Read perlsec for more information and mind opening.

      Open source softwares? Share and enjoy. Make profit from them if you can. Yet, share and enjoy!

      And how do you call the stored procedure and provide parameter values? Not by interpolating strings in the SQL I hope.

      If you don't use string interpolation, I guess you use placeholders, and we're back to the advice: always use placeholders.

      The use of SPs are orthogonal to that issue.

      /J

Re: Preventing SQL injection attacks: are -T and placeholders not enough?
by Errto (Vicar) on Jan 09, 2008 at 19:23 UTC

    This may be stating the obvious, but placeholders are only guaranteed effective when the SQL itself is completely static or, at the very least, is assembled from components that are themselves completely static - that is, no user-supplied data enters the SQL string itself in any way.

    I bring this up because there may be situations where some portion of the statement (for ex. a column name) needs to be dynamically determined based on inputs. Such situations definitely require extra care. Also, it helps to emphasize the contrast with certain technologies (*cough* PL/SQL *cough*) that have less than full support for placeholders :)

      Fortunately, non-static SQL is often a sign of evil database design :)

        Oddly, however, knowing that your database design is evil doesn't necessarily help you write more secure code for it. ;-)



        If God had meant us to fly, he would *never* have given us the railroads.
            --Michael Flanders

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://661249]
Approved by kyle
Front-paged by duelafn
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (8)
As of 2014-07-28 09:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (193 votes), past polls