How to create select/update/delete statement using stored procedure safe from SQL injection

Question

I always prefer to use stored procedures for most SQL commands during development. One example for select statement.I use this Store porcedure

ALTER proc [dbo].[sp_select] (@tbl varchar(200),@col varchar(max),@cond varchar(max))
as
declare @query varchar(max)
if(@cond!=NULL)
begin
set @query='select '+@col+' from '+@tbl+' where '+@cond
end
else
begin
set @query='select '+@col+' from '+@tbl 
end
exec(@query)
GO

I am little conscious SQL Injection atacks. This way is safe from such attack or not??

Any suggestion would be appreciated...

Answer 1

Your stored procedure is completely pointless and only makes it harder to write safe code.

SQL injection is not magic; it's simply input strings with quotes.
Stored procedures do not magically defend against it; they simply encourage you to pass user input as parameters (which you aren't doing).

The correct way to protect against SQL (and other forms of) injection is to change your application code to never concatenate arbitrary text (especially user input) into a structured langauge (such as SQL, HTML, or JSON).

Instead, use parameters, a JSON serializer, or a proper HTML escaper, as appropriate.