-1

I've read that calling sp_executesql leaves your SQL Server vulnerable to hackers. Is there a realistic alternative that gives the same flexibility? (I'm of the opinion that the database should only be used to store the data but everywhere that I have worked wants business logic via the database.)

SlimsGhost
  • 2,849
  • 1
  • 10
  • 16
Paul McCarthy
  • 818
  • 9
  • 24
  • 5
    Please post a reference to what you have read. – Gordon Linoff May 08 '17 at 14:52
  • @GordonLinoff https://msdn.microsoft.com/en-us/library/ff648339.aspx – Intern87 May 08 '17 at 15:04
  • @Intern87 . . . The issue isn't `sp_executesql`. The issue is executing code provided by a user. That is only a very small part of what `sp_executesql` does. If used correctly, it is not dangerous. – Gordon Linoff May 09 '17 at 01:01
  • @GordonLinoff I agree with you, with much of the things exploited by SQL injection, it's just careless implementation – Intern87 May 09 '17 at 06:08
  • Ref: http://stackoverflow.com/questions/14722201/stored-procedure-exec-vs-sp-executesql-difference one of the comments has "Every time you call sp_executesql you're potentially letting every joe hacker on the Internet into your server" – Paul McCarthy May 09 '17 at 15:54

2 Answers2

2

sp_executesql is just as safe as pretty much every other method for issuing dynamic SQL. If you parameterize your user-provided inputs (e.g. values for where clauses, and other arguments), then it is fairly secure. If you don't, it is potentially open to SQL injection attacks. There is nothing that I know of that makes sp_executesql any less secure than other approaches to dynamic sql.

Note that putting business logic in the database can also mean that stored procedures are used. These are usually a little easier to control than allowing arbitrary SQL to be issued (even if it is parameterized correctly). Of course anything can be implemented poorly and still have vulnerabilities, but there are other advantages to using stored procedures and minimizing the use of truly dynamic SQL.

Check out the MSDN article describing how to use parameters with sp_executesql

SlimsGhost
  • 2,849
  • 1
  • 10
  • 16
1

follow these guidelines and you should be ok; https://msdn.microsoft.com/en-us/library/ff648339.aspx

Review Your Application's Use of Parameterized Stored Procedures Because using stored procedures with parameters does not necessarily prevent SQL injection, you should review your application's use of this type of stored procedure. For example, the following parameterized stored procedure has several security vulnerabilities.

 CREATE PROCEDURE dbo.RunQuery @var ntext AS
        exec sp_executesql @var GO

An application that uses a stored procedure similar to the one in the preceding code example has the following vulnerabilities: The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to: DROP TABLE ORDERS; In this case, the ORDERS table will be dropped. The stored procedure runs with dbo privileges. The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to probe the database, he or she will see the name of the stored procedure. With a name like RunQuery, he can guess that the stored procedure is likely to run the supplied query.

The safer alternative is to run the stored procedure with validated parameters

Intern87
  • 469
  • 1
  • 6
  • 18