Security with Uploading CSV to update database

[jerrygarciuh]

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

[DamnDirtyApe]

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

[dws]

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

[jerrygarciuh]

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

[Ovid]

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.

[Tomte]

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

---