1

Today I faced the worst situation. There is a stored procedure in database, I had to alter it, using inline query,

Everything went fine by executing this query:

DECLARE @script AS NVARCHAR(MAX)

SET @script = 'ALTER PROCEDURE GetALL
               with recompile
               AS
               BEGIN
                   SELECT *  
                   FROM dbo.Main
                   INNER JOIN dbo.alabla ON dbo.Main.ID = dbo.ATTENDANCE.EMPLOYEE_ID
               END'

EXEC sp_executesql @script ;

Above query has a syntax error alabla, the table does not exists but EXEC sp_executesql @script ; performed without any error.

and when I tried to use this stored procedure, I get an error:

Msg 208, Level 16, State 1, Procedure GetALL, Line 5
Invalid object name 'dbo.alabla'.

Can anybody tell here that how we make sure that inline query execution is safe?

DareDevil
  • 5,249
  • 6
  • 50
  • 88
  • 3
    Why do you have to alter this using sp_executeSQL? – Paddy Jan 08 '16 at 11:35
  • Because the requirement over network, – DareDevil Jan 08 '16 at 11:37
  • 1
    What does "requirement over network" mean? – Tanner Jan 08 '16 at 11:38
  • 3
    That's the default behavior in T-SQL - if you create a stored procedure, only **syntactical** checks are performed - there's no checking whether those tables you reference in your stored procedure exist or not (and there's also no option to turn such checking on). You'll only ever know at runtime. – marc_s Jan 08 '16 at 11:40
  • I don't have access of the server and we have to send this using a page, that carries inline script and it then executes. – DareDevil Jan 08 '16 at 11:40
  • 2
    Even if you install procedures directly without sp_executesql you won't get errors on select statements for missing tables, but if I remember correctly, you will for update / insert. There is a connect item were people hope to get this improved, maybe you should vote it too: https://connect.microsoft.com/SQLServer/Feedback/Details/260762 – James Z Jan 08 '16 at 11:40
  • This is what happened today,:( – DareDevil Jan 08 '16 at 11:46
  • That's some very silly security there. You don't have access to the server directly, but you do have access to a page that allows you to enter and execute arbitrary SQL. Nice. – Paddy Jan 08 '16 at 12:22
  • because that page has some security checks after then it enables a server's script execution. – DareDevil Jan 08 '16 at 12:26

1 Answers1

1

Have a look here: How to precompile stored procedures in SQL server?

sp_recompile will only mark a method to be recompiled the next time it is called...

If your newly created procedure does not manipulate data (your example above) you might just call it immediately...

Best hint could be to have a look on SET FMTONLY even if it's deprecated (https://msdn.microsoft.com/de-de/library/ms173839(v=sql.120).aspx) or you might use sp_describe_first_result_set (and related: see the links on the page mentioned).

This should force an immediate compilation...

Furthermore have a look on

  • DBCC CHECKDB
  • DBCC FREEPROCCACHE
  • DBCC PROCCACHE

btw: I would not use a SP if you only want to read data. Your example above would be a perfect candidate for an inlineable (single-statement, ad-hoc) TVF...

Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • It was just an example to show the behavior, otherwise purpose was different to alter SP. – DareDevil Jan 08 '16 at 13:09
  • @DareDevil, To be honest: If you send a script to somewhere and execute it there, you may force some kind of immediate compilation to find a syntax error as above, **but**: What you really should do is to install this in your local test system and check it there. It should be absolutely impossible to send such a script to a customer's db if it's result is not compilable... – Shnugo Jan 08 '16 at 13:12
  • you are right, there should be a check or immediately calling the routine in try-catch to see the behavior. – DareDevil Jan 09 '16 at 15:58
  • OK mate I will, was my question worthwhile?people don't vote it too, sometimes they down vote, which I really don't know. Why – DareDevil Jan 09 '16 at 16:06
  • @DareDevil, I do vote on questions quite often... Don't really know why I did not here... Alltogether it's much easier to get reputation points here with script-kiddy android question (and answers!) than with highly elaborated complex answers on specific questions... But that's SO :-) – Shnugo Jan 09 '16 at 16:10
  • I really don't question for the sake of points but for a better answer or clarity – DareDevil Jan 09 '16 at 16:12
  • @DareDevil, if you hover over the vote marks you find hints like "research effort", clear question and it should be useful. Quite often question are really not much more than "How do you...."? Sometimes there are really silly newbie questions with tons of answers, votes and comments and sometimes questions are closed due to duplicates which are 6 years old... But I like SO anyway :-) – Shnugo Jan 09 '16 at 16:35