Solved

Am I protect against SQL Injection?

Posted on 2008-10-30
7
148 Views
Last Modified: 2010-03-20
Hello,

I have read several articles on MSDN, here at Experts-Exchange, etc. to better understand SQL injection and prevention standards.  Below is a result of this research.  However, I would appreciate it if someone could confirmed that this is the path I need to take.

Thank you in advance.
CREATE PROCEDURE [dbo].[sp_addNavElement]
(
	@navID varchar(30), 
	@navURL varchar(30)
)
 
AS 
 
DECLARE @cmd nvarchar(max)
DECLARE @parameters nvarchar(max)
 
SET @cmd = N'INSERT INTO navSystemA (navID, navURL, navOrder) Values (@navID, @navURL, (select count(*) from navSystemA)+ 1 )'
 
SET @parameters ='@navID varchar(30), @navURL varchar(30)'
EXEC sp_executesql @cmd, @parameters, @navID, @navURL

Open in new window

0
Comment
Question by:trumpman
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22849594
it would be a lot safer to use a Stored Procedure instead of a dynamic query. But since you are limiting the lenght to 30 characters, not much can be done.
0
 

Author Comment

by:trumpman
ID: 22849941
@emoreau:

Perhaps I am missing something?  This is a Stored Procedure.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 22850108
No your not missing anything. I was! As long as you are calling this SP using Command objects, you should not have problems.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 22

Expert Comment

by:dportas
ID: 22854276
This is a poor example because the dynamic SQL is completely unnecessary - it would be better just to put the INSERT in the proc without EXEC or sp_executesql. Either way, it isn't vulnerable to SQL injection - it's just inefficient and poor practice.

PS. Never use the "sp_" prefix for user procs. sp_ is reserved for system procs and will adversely affect your code.
0
 

Author Comment

by:trumpman
ID: 22855740
@dportas:

Could you elaborate on your suggested method?  .."better just to put the INSERT in the proc without EXEC or sp_executesql"...

Thank you.  How would the desired result look?

- Trumpman
0
 
LVL 22

Accepted Solution

by:
dportas earned 260 total points
ID: 22856084
CREATE PROCEDURE [dbo].[prc_addNavElement]
(
      @navID VARCHAR(30),
      @navURL VARCHAR(30)
)
AS
BEGIN;
      INSERT INTO navSystemA (navID, navURL, navOrder)
      VALUES (@navID, @navURL,(SELECT COUNT(*) FROM navSystemA)+ 1 );
END;
0
 
LVL 2

Assisted Solution

by:devshb
devshb earned 240 total points
ID: 22856308
In theory I think you're still open to xss attacks, so by using the logic you've got I think you're pretty much covered against direct injection, but people could still use script tags etc, so you might also want to clean the value of any < or > characters etc during the insert, although it depends on the nature of the data.

See also the "resources" section on the site:
http://www.sqlinjectionscanner.com/
which explains the difference between xss attacks and direct injection
(and that site also has a free data scanner)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question