Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Security with Uploading CSV to update database

by jerrygarciuh (Curate)
on Aug 12, 2002 at 21:03 UTC ( #189630=perlquestion: print w/ replies, xml ) Need Help??
jerrygarciuh has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed Monks,
My masters desire a script which can be used to browse for a CSV, upload it, and execute a mySQL update using it's contents.

My skills allow me to code this without issue using Text::CSV and (obviously) DBI and CGI. The script will be protected by .htaccess and it's own seperate password. The script will unlink or relocate the CSV after a successful update.

I still feel I must be missing some deeper security issue. Does anyone see one from this synopsis?
TIA
jg
_____________________________________________________
Think a race on a horse on a ball with a fish! TG

Comment on Security with Uploading CSV to update database
Download Code
Re: Security with Uploading CSV to update database
by DamnDirtyApe (Curate) on Aug 12, 2002 at 21:09 UTC

    AFAIK, using an .htaccess file will prevent unauthorized access to your CSV file while it's on your server, but does nothing to protect its contents while in transit to your client. For that, you might use SSL (though I know nothing about setting it up), or change the method by which you retrieve the file (scp, a secure copying program bundled with ssh, may be an option for you.)

    HTH
    _______________
    DamnDirtyApe
    Those who know that they are profound strive for clarity. Those who
    would like to seem profound to the crowd strive for obscurity.
                --Friedrich Nietzsche
Re: Security with Uploading CSV to update database
by dws (Chancellor) on Aug 12, 2002 at 21:11 UTC
    ... a script which can be used to browse for a CSV, upload it, and execute a mySQL update using it's contents.

    Could you expand on this a bit? As stated, I can read this two ways*. One is that the files being browsed for are on some local user's machine (e.g., using a file upload form), and that they're uploaded to a server where they're using to update a MySQL database. The other is that they're browsed from on the server, and are uploaded to a client workstation that has a MySQL database.

    Assuming the former, you could use CGI.pm to handle a file upload, and then use TextCSV to pick it apart, loading it into MySQL. These are nicely separable problems.

    The security problem you have to deal with is someone uploading bogus data. You can approach this either by restricting who can upload data (e.g., using the .htaccess method you propose), or by rigorous checking of the data before updating the database, or both.

    *Learning to read requirements in conflicting ways is a useful skill for staying out of trouble.

      Thanks for the reply dws. Your assumption is correct. One of the handlers of the main database will be exporting the csv's and then using my script to upload the file and execute the update.

      Assuming that someone defeats the double password protection and attempts to upload bogus data, they will have to figure out how many fields there are (I do a check on the CSV data), an incorrect # of fields throws an error and a message like: "Error #553. Please contact Jerry Garciuh at Terrapin Station". What sort of more rigorous checking do you think should I employ?
      Thanks again
      jg
      _____________________________________________________
      Think a race on a horse on a ball with a fish! TG

Re: Security with Uploading CSV to update database
by Tomte (Priest) on Aug 12, 2002 at 21:15 UTC

    I'd say you should make sure that neither a parameter nor the files content can be used against your script in a harmful way, by checking thoroughly for their correctness, that is, don't make the assumption that it will always be CSV-file you're getting

    To find anything like a deeper security issue, I for one would need a bit of code to grep through ;-), other monks will be more helpful I suppose (me being a scribe still...both here in the monastery and in using perl)
    regards,
    tomte


Re: Security with Uploading CSV to update database
by Ovid (Cardinal) on Aug 12, 2002 at 21:50 UTC

    First, I would recommend using Text::CSV_XS instead of Text::CSV. The former can handle embedded newlines and the latter cannot.

    Second, you should validate the actual data that you are stuffing the database. I've written a CSV Database Validation program that you can modify for this purpose. Amongst other things, it allows you to specify unique constraints, foreign keys, and even specify a regex as a field's datatype (thus allowing much richer validation that a simple INT or VARCHAR).

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (8)
As of 2014-12-25 12:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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





    Results (160 votes), past polls