SQL Server store procedure and prevent sql injection
-
i see this article How to prevent SQL Injection in Stored Procedures[^] i compose my code like that way but not save from sql injection in my code. what is wrong there in my code ?
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='like ''%ra%''';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name '+@Name;
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;suppose i have store proc where i will send name when i will call store proc. so we can send parameter value like 'like ''%ra%''' sp_executesql not saving me from injection problem. guide me how to prevent injection in procedure. thanks
-
i see this article How to prevent SQL Injection in Stored Procedures[^] i compose my code like that way but not save from sql injection in my code. what is wrong there in my code ?
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='like ''%ra%''';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name '+@Name;
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;suppose i have store proc where i will send name when i will call store proc. so we can send parameter value like 'like ''%ra%''' sp_executesql not saving me from injection problem. guide me how to prevent injection in procedure. thanks
Simple - don't use string concatenation to build your query.
DECLARE @Name NVARCHAR(50);
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);SET @Name = '%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name';
SET @params = N'@Name NVARCHAR(50)';EXECUTE sp_executesql @sqlcmd, @params, @Name;
Now the question is, why are you using dynamic SQL for such a simple query?
DECLARE @Name NVARCHAR(50);
SET @Name = '%ra%';
SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Simple - don't use string concatenation to build your query.
DECLARE @Name NVARCHAR(50);
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);SET @Name = '%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name';
SET @params = N'@Name NVARCHAR(50)';EXECUTE sp_executesql @sqlcmd, @params, @Name;
Now the question is, why are you using dynamic SQL for such a simple query?
DECLARE @Name NVARCHAR(50);
SET @Name = '%ra%';
SELECT * FROM [TestDB].[dbo].emp1 WHERE Name Like @Name;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not. so tell me what is way out see my fresh code where %ra% is working instead of protection.
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name; -
i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not. so tell me what is way out see my fresh code where %ra% is working instead of protection.
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;Mou_kol wrote:
i thought sp_executesql will prevent that but did not.
"sp_executesql" executes sql. The SQL-command has no need to check for injection by the user, as most users will not directly access the database. There is no way you can add strings and magically secure them.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not. so tell me what is way out see my fresh code where %ra% is working instead of protection.
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;Mou_kol wrote:
see my fresh code where %ra% is working instead of protection.
What do you mean? :confused: If you mean it's matching records based on the wildcards, then that's exactly what you've told it to do. There's no SQL Injection there. Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^] How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^] Query Parameterization Cheat Sheet | OWASP[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Mou_kol wrote:
i thought sp_executesql will prevent that but did not.
"sp_executesql" executes sql. The SQL-command has no need to check for injection by the user, as most users will not directly access the database. There is no way you can add strings and magically secure them.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Tenho o mesmo problema! tenho até site mydogfit.com
-
i just try to simulate how to prevent sql injection in sql server store proc but i saw it did not work. %ra% works. i thought sp_executesql will prevent that but did not. so tell me what is way out see my fresh code where %ra% is working instead of protection.
DECLARE @Name NVARCHAR(50)
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @Name ='%ra%';
SET @sqlcmd = N'SELECT * FROM [TestDB].[dbo].emp1 WHERE Name like @Name';
SET @params = N'@Name NVARCHAR(50)';
EXECUTE sp_executesql @sqlcmd, @params, @Name;The primary use of sp_ExecuteSQL is when you the Database Developer absolutely needs to create Dynamic SQL, and not for protection. While what you have written appears safe, there is no need for Dynamic SQL and you are only adding a layer of service and reducing performance.
Director of Transmogrification Services Shinobi of Query Language Master of Yoda Conditional
