Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: Preventing malicious T-SQL injection attacks

by Moron (Curate)
on Mar 05, 2007 at 13:07 UTC ( #603205=note: print w/ replies, xml ) Need Help??


in reply to Preventing malicious T-SQL injection attacks

The traditional solution, both for MS SQL Server and Sybase is to grant the ordinary database user execute privilege - but nothing else! Then all insert/update/delete/select can only be performed by executing procs written by the privileged users. It means writing four access procedures per logical table, but these can be templated and generated from Perl.

-M

Free your mind


Comment on Re: Preventing malicious T-SQL injection attacks
Re^2: Preventing malicious T-SQL injection attacks
by smithers (Friar) on Mar 05, 2007 at 18:41 UTC
    We do as Moron++ prescribes but it's easier said than done. You need discipline to only grant execute access to SQL or Windows logons for the s-procs (direct base-table or view access is not allowed) and write your s-proc code accordingly. Your security model can easily get botched by folks that don't understand the details and grant db_datareader/db_datawriter in a troubleshooting panic and don't understand the long term repercussions of such an action.
      Yes, for example, it means programmers can't go issuing any SQL they like from the client programs but have to put all SQL in stored procedures and execute it with parameters determined at the client end. One source of this strategy was back in the heyday of C++ in the 1990s when programmers (with usually more C knowledge than database knowledge) were apt to embed all SQL in their client programs which then tended to cause objects to deadlock each other and even themselves in some cases and also to prevent unecessary communication across the network. Rather than have a client program sending SQL requests results and getting sometimes huge result sets over the net and then processing them, it is more efficient overall to let the client program handle the client functionality and stored procedures handle the server functionality - but there are many people out there who want to be able to code everything from the only language they know and that is a tendency especially we as Perl programmers with all our many modules also need to avoid sometimes.

      -M

      Free your mind

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (11)
As of 2014-12-21 15:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

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





    Results (106 votes), past polls