Is this vulnerable to SQL injection?

Question

We are using a third party product which references a stored procedure in MSSQL. This stored proc looks something like this:

CREATE PROCEDURE [dbo].[example]
 @a nvarchar(255)
 AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @strSQL nvarchar(3000)
  SET @strSQL = 'SELECT * FROM test WHERE x = ''1'''

  IF IsNull(@a, '') <> ''
    SET @strSQL = @strSQL + ' AND a = ''' + @a + ''''
  EXEC(@strSQL)
END

This stored proc doesn't actually output its results to the website but I'm still sure that it is vulnerable to SQL injection. I can input t' + 'est and get the same result as I would from inputing test.

We obviously need to get them to change this but I need to demonstrate that it is an issue first. How can I do something like insert a row in to a table by passing SQL in as @a? If I do

'; INSERT INTO blah VALUES('test')

Then I get:

Incorrect syntax near ';'.

Answer 1

yes, it's vulnerable, but by chance you've injected the wrong text, producing a syntax error:

SELECT * FROM test WHERE x = "1" AND a =; INSERT INTO blah VALUES('test')
                                        ^--your syntax error

If your injection text had been:

a; INSERT blah blah blah
^---

then you'd have ended up with two valid queries and test in your blah table.