Geek asks and answers

Invalid column name in SQL server using Dynamic SQL

When I try to run SQL, it does not format the value of the @Status parameter with single quotes, as it is text. Therefore, this error is listed as Startup - an invalid column name.

DECLARE
    @ID int,
    @Status varchar(150),
    @StandardOutput varchar(max) = NULL,
    @StandardError varchar(max) = NULL,
    @Query Varchar(max),
    @S1 varchar(max),
    @S2 varchar(max),
    @S3 varchar(max)


SET     @Status = N'Running'
SET     @StandardError = N'So2234'
SET     @StandardOutput = Null
SET @S1 = ''
SET @ID = 1
--DECLARE @S1 varchar(max)
--SET @S1 = N' '
IF @Status IS NOT NULL 
    BEGIN
    SET @S1 = ( N',  dbo.JobQueue.Status = ' + @Status);
    END
IF @StandardError IS NOT NULL 
    BEGIN
    SET @S1 = @S1 + N',  dbo.JobQueue.StandardError = ' + @StandardError
    END
IF @StandardOutput IS NOT NULL
    BEGIN
    SET @S1 = N', dbo.JobQueue.StandardOutput = ' + @StandardOutput
    END

SET @S1 = (N' UPDATE  dbo.JobQueue SET ' + SUBSTRING(@S1, 2, LEN(@s1)) ) + ' ';
SET @S1 = @S1 + N' WHERE  dbo.JobQueue.ID = ' + CONVERT(VARCHAR(12), @ID);
SELECT @S1
EXEC(@S1)

Msg 207, Level 16, State 1, Line 1
Invalid start column name.
Msg 207, Level 16, State 1, Line 1
Invalid column name "So2234"

PRINT RESULT @ S1

UPDATE  dbo.JobQueue SET   dbo.JobQueue.Status = Running,  dbo.JobQueue.StandardError = So2234  WHERE  dbo.JobQueue.ID = 1
+3
source share
3 answers

, . , "Running" , (SQL Injection)

:

IF @Status IS NOT NULL 
    BEGIN
    SET @S1 = ( N',  dbo.JobQueue.Status = ''' + @Status + ''');
...
+1

SELECT @S1.

! , - , -.

Update:

UPDATE  dbo.JobQueue SET   dbo.JobQueue.Status = Running,  dbo.JobQueue.StandardError = So2234  WHERE  dbo.JobQueue.ID = 1

So2234 - , !

+1

Why aren't you using non-dynamic sql here?

UPDATE jq
SET Status = ISNULL(@status, jq.Status)
    ,StandardError = ISNULL(@StandardError, jq.StandardError)
    ,StandardOutput = ISNULL(@StandardOutput, jq.StandardOutput)      
FROM dbo.JobQueue jq
WHERE jq.ID = @id
0
source

More articles:


All Articles