November 23, 2022 at 7:54 am
Hi All,
We are having sql injection from website. The login used in the connection having only data reader and execute all SP. We use SP, view and function, no adhoc query. To find we are running server side trace. From that, I have found someone executed SP where this sp do number of items like select, insert and update based on action parameter passed etc. It accepts more than 10 parameters.
exec sp_xxx_name @action=N'select',@U_Id=N'xxx_transfer',@Password=N'xxxx'.
exec xx_xx_Login @id=N'11xx',@pass=N'xxxx'
The login used is connection string login and app is .Net SqlClient Data Provider NT user is NETWORK SERVICE.
Any suggestion, how to prevent and make our code better etc.
November 23, 2022 at 8:02 am
don't use dynamic sql in your stored procedures! ( or use only parameterized queries with the semi generated sql )
don't grand datareader/datawriter !! Only grant exec for sprocs it is allowed to use and have those sprocs use "with execute as ..." to provide the needed authorizations for that sproc )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 23, 2022 at 8:59 am
Thanks Johan.
Can you share some example of parameterized stored proc.
Ex: something like this.
Create sp_name (@u_id int,@pass varchar(100))
as
begin
declare @l_u_id int
declare @l_pass varchar(100)
code here
end
2. If I remove reader, how he login used in application can read data, by stored proc.
November 23, 2022 at 9:40 am
November 23, 2022 at 12:47 pm
When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.
😎
If the procedure does not return a result set description, then don’t run the code 😉
November 23, 2022 at 1:01 pm
It's a soup to nuts, everything is on the table, proposition to really get proper protection from SQL Injection.
First, and most important, use the least-priviledge principal when setting up your security. Only the access things MUST have should be allowed for any given login or role. Too much access is the biggest problem.
Next, use stored procedures (as was already mentioned), or, parameterized queries such that you're not building and executing strings of SQL code. Along with this, use the correct data types. If it's a number, use a number, not a string. You need a date or time, use a date or time data type, not a string. In addition, as Eirikur says, validate the input on the code side of things to ensure that dates are dates, etc..
Additionally, SQL Injection requires exploration. There are common errors, make darned sure you're monitoring for them. And, speaking of errors, ensure that you have appropriate error trapping in place such that it's not exposing details of your infrastructure when errors occur, a common approach to understanding if you can be hacked.
Do all this, and you'll be protected. Just some of it isn't enough. I've seen code where it's all in stored procedures, but you're passing giant strings and doing ad hoc query builds instead of parameterized queries. However, it all starts with the right security settings. Just that, and only that, can minimize the harm of a SQL Injection attack if you do nothing else. However, I'd suggest pursuing everything. Defense in depth.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 23, 2022 at 3:07 pm
When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.
😎
If the procedure does not return a result set description, then don’t run the code 😉
Thank you, can you refer any example for this method.
November 23, 2022 at 3:28 pm
@Raja mssql ,
This is meant as a friendly but serious suggestion... IF you even think that you're experiencing "SQL Injection" and YOU are asking these types of questions on a forum, then you folks are the wrong ones to handle it. You folks need to stop guessing and hire an expert company to look at your systems and DodGammit and fix it. Then, also hire them to run PenTesting on a regular basis so that you folks don't end up reading about yourselves on the internet.
On the latter... you should do that even if you don't believe you're experiencing "SQL Injection" or think that your systems are immune from it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2022 at 12:36 pm
Eirikur Eiriksson wrote:When it comes to validating users’ input, dynamic SQL or not, I like to use the belt and braces method of validating the input by running it through the sys.dm_exec_describe_first_result_set procedure before executing the code.
😎
If the procedure does not return a result set description, then don’t run the code 😉
Thank you, can you refer any example for this method.
😎
Seriously advise you to carefully read Grant's and Jeff's fine comments and act accordingly!
November 24, 2022 at 1:53 pm
Thanks everyone. I will speak to the team and see what we can do.
December 2, 2022 at 11:03 am
There's a good explanation of SQL injection (and how to prevent it) here.
https://www.sqlinthewild.co.za/index.php/2009/04/03/dynamic-sql-and-sql-injection/#more-230
December 14, 2022 at 6:24 am
ok, got it. Today I will put it into practice.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply