CODEWITHSUNDEEP
sqlsql-server
Pubjer19
Pubjer19

Reputation: 29

Building dynamic SQL command - Incorrect syntax error

I want to build a SQL command dynamically by checking if a parameter is null. If it is not null, it will be added to the command string. The stored procedure can be compiled, but at running time I get an invalid syntax error. Here is the code I've written so far:

CREATE PROCEDURE searchEvents @name VARCHAR(50), @location VARCHAR(20), @postcode CHAR(4), @address VARCHAR(40), @startDate DATETIME, @endDate DATETIME
AS
DECLARE
@sqlCommand NVARCHAR(MAX) = 'SELECT Event.Name, Description, Location.Name AS Location, Postcode, Address, StartDate, EndDate, Website FROM Event JOIN Location ON Event.LocationID = Location.LocationID',
@parameters NVARCHAR(MAX),
@whereIncluded BIT = 0
BEGIN
IF @name IS NOT NULL
    BEGIN
        IF @whereIncluded = 0
        BEGIN
            SET @sqlCommand = @sqlCommand + ' WHERE '
            SET @whereIncluded = 1
        END
        ELSE
            SET @sqlCommand = @sqlCommand + ' AND '

        SET @sqlCommand = @sqlCommand + 'Event.Name LIKE ' + '%' + @name + '%'
    END

-- It's the same if clause for all parameters like above

SET @parameters = N'@p_name VARCHAR(50), @p_location VARCHAR(20), @p_postcode CHAR(4), @p_address VARCHAR(40), @p_startDate DATETIME, @p_endDate DATETIME'

EXEC sp_executesql
@sqlCommand,
@parameters,
@p_name = @name,
@p_location = @location,
@p_postcode = @postcode,
@p_address = @address,
@p_startDate = @startDate,
@p_endDate = @endDate
END

The query I execute:

EXEC searchEvents 'Wine fair', NULL, NULL, NULL, NULL, NULL

The error I get:

Incorrect syntax near "Wine fair".

Upvotes: 1

Views: 180

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46231

Add a PRINT statement for debugging and the reason for the error will be clear:

WHERE Event.Name LIKE %Wine fair%

As you can see, the single-quotes around the intended literal are missing.

Note that this code is vulnerable to SQL injection. If you only add the missing quotes and use string concatenation, malicious SQL can be injected. Instead, specify the parameter value directly in the generated SQL statement rather than a string literal. The example below also escapes LIKE wildcards so that the desired results are returned even if the user-supplied value contains them.

SET @name = REPLACE(@name,'%', '[%]'); --escape % wildcard
SET @name = REPLACE(@name,'_', '[_]'); --escape _ wildcard
SET @name = REPLACE(@name,'[', '[[]'); --escape [ wildcard
SET @name = '%' + @name + '%' --add desired wildcards
SET @sqlCommand = @sqlCommand + 'Event.Name LIKE @name'; --use parameter directly in SQL statement

Also make sure the application runs under a minimally-privileged account. For maximum security, use sign the stored procedure with a certificate associated with a user with the needed SELECT permissions. That way, the app only needs execute permissions on the proc.

Upvotes: 3

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

You missed quotes

 SET @sqlCommand = @sqlCommand + 'Event.Name LIKE ' + '''%' + @name + '%'''

UPD: To avoid SQL injection:

SET @sqlCommand = @sqlCommand + 'Event.Name LIKE ''%'' + @p_name + ''%'''

Upvotes: 1

Related Questions