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

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?
Think a race on a horse on a ball with a fish! TG

Replies are listed 'Best First'.
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.)

    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 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
      Think a race on a horse on a ball with a fish! TG

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


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

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)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://189630]
Approved by vek
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (5)
As of 2017-10-20 00:58 GMT
Find Nodes?
    Voting Booth?
    My fridge is mostly full of:

    Results (258 votes). Check out past polls.