Calling Variable in T -SQL

Question

What is diff in below 2 query?? Which one is better in term of performance and why??

declare @id varchar(10) ='207054'

declare @query nvarchar(max)

Set @query= 'select top 1 * from practice where externalid=@id'
print @query
Exec Sp_executeSQL @query,N'@id varchar(10)',@id

Set @query= 'Select top 1 * from practice
             where externalid='''+@id+''' '
print @query
Exec Sp_executeSQL @query

Concatenating SQL string is dangerous. SQL Injection example: rextester.com/HSO30582 — Lukasz Szozda
– Lukasz Szozda, Commented Jun 29, 2017 at 5:43

Joel Coehoorn · Accepted Answer · 2017-06-29 05:42:11Z

2

It depends on how @id is populated. If the value of that variable comes from the user, the 2nd option opens a huge gaping security hole (see: Sql Injection), while the first does not.

Better to be safe... what today is a known-safe source, suddenly tomorrow is used in an unsafe way, and suddenly your database is hacked. Prefer the first option whenever possible.

answered Jun 29, 2017 at 5:42

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calling Variable in T -SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related