Bulk Insert CSV into SQL Server 7

PHPBewildered

Hello folks. I am hoping someone can provide advice on the preferred method for uploading a CSV file to a SQL Server 7 database using PHP 4.3.4.

I would like to avoid having to grant the user any privileges that aren't absolutely necessary. Using the BULK INSERT statement (T-SQL) requires sysadmin privileges so I am looking for a suitable alternative.

Is ADO a good approach for this type of application?

AstroTeg

I was thinking to suggest ISQL from the command prompt (assuming its been installed from the MS SQL Server CD). Using ISQL, you could import the CSV file. I believe you can run ISQL as another user. Don't know if this leads you to any new ideas or not.

The only other way I can think of is to write code to handle reading each line of the CSV file and then inserting it into the database...

Roger_Ramjet

I would have said that the 'prefered' method was to use the MS SQL Server functions , in particular the mssql_execute to run a stored proc. Your stored proc could be given public execution rights so users could upload the file without being granted additional rights.

My own 'secure' approach would be to allow the user to execute a proc which uploaded to a temp table/file first, and then verify the data before insert. Pre-empt any sql injection as the attack would be upon tempdb not your live db.