Solved

Am I protect against SQL Injection?

Posted on 2008-10-30
7
147 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 69

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 69

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now