C# SqlCommand command with parameter in the middle of query

Question

I'm having an issue with inserting parameter through a SQLCommand in C#. When parameter legal_GUID was in the end of query it works well.

string[] param = getHTTPparams(connection);
SqlCommand command = new SqlCommand(@"SELECT AD.document_GUID ,AT.attachment_fileName ,do
                          FROM [legal].[mdm].[View_All_Documents] AD
                          LEFT JOIN [legal].[dbo].[AllAttachments] AT
                          ON AD.document_GUID = AT.document_GUID
                          WHERE AD.legal_GUID = '" + legal_GUID + "'", connection);

But when i am trying to make changes to query and current parameter legal_GUID moves to the middle of the query it doesn't work.

SqlCommand command = new SqlCommand(@"DECLARE @order_guid_tr uniqueidentifier
                               SELECT 1 @order_guid_tr = Order_guid
                               FROM [legal].[mdm].[View_All_Documents]
                               WHERE legal_GUID = '" + legal_GUID + "'
                               SELECT AD.document_GUID ,AT.attachment_fileName, document_type
                           FROM [legal].[mdm].[View_All_Documents] AD
                           LEFT JOIN [legal].[dbo].[AllAttachments] AT
                           ON AD.document_GUID = AT.document_GUID
                           WHERE AD.ORDER_GUID = @order_guid_tr", connection);

What changes do I need to make to the query syntax, what do I miss?

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Jan 19, 2018 at 13:28
"Doesn't work" how? What fails? It looks like you're trying to run multiple queries as one. Why? Also, why aren't you using query parameters? — David
– David, Commented Jan 19, 2018 at 13:29
The 1 in SELECT 1 @order_guid_tr = Order_guid... doesn't look right. — Iridium
– Iridium, Commented Jan 19, 2018 at 13:31
I think you should put a semicolon between the two statements. Not sure if that fixes the issue though. — Nisarg Shah
– Nisarg Shah, Commented Jan 19, 2018 at 13:31

Thomas Voß · Accepted Answer · 2018-01-19 13:47:36Z

2

As Avitus already mentioned it is unsafe to put parameters into the query by concatenating strings. You should better use the Parameters of the SqlCommand for that:

        string sql = "SELECT AD.document_GUID, AT.attachment_fileName, document_type
                      FROM [legal].[mdm].[View_All_Documents] AD LEFT JOIN [legal].[dbo].[AllAttachments] AT
                      ON AD.document_GUID = AT.document_GUID
                      WHERE AD.ORDER_GUID IN (SELECT TOP 1 Order_guid FROM [legal].[mdm].[View_All_Documents] WHERE legal_GUID = @legal_GUID)";

        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = sql;
        cmd.Parameters.Add("@legal_GUID", SqlDbType.UniqueIdentifier).SqlValue = legal_GUID;

answered Jan 19, 2018 at 13:47

Thomas Voß

1,1658 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avitus · Accepted Answer · 2018-01-19 13:38:38Z

0

When you do inline sql like that you have to let the parser know that it's a new statement. You can either do that with a space, carriage return or semi colon. Also you're missing the keyword top to get the first record from View_All_Documents

If you change to be:

SqlCommand command = new SqlCommand(@"DECLARE @order_guid_tr uniqueidentifier;
                               SELECT top 1 @order_guid_tr = Order_guid
                               FROM [legal].[mdm].[View_All_Documents]
                               WHERE legal_GUID = '" + legal_GUID + "';
                               SELECT AD.document_GUID ,AT.attachment_fileName, document_type
                           FROM [legal].[mdm].[View_All_Documents] AD
                           LEFT JOIN [legal].[dbo].[AllAttachments] AT
                           ON AD.document_GUID = AT.document_GUID
                           WHERE AD.ORDER_GUID = @order_guid_tr", connection);

It should work but this is very bad to do. By doing this you allow for a sql injection attack against your code.

edited Jan 19, 2018 at 13:38

answered Jan 19, 2018 at 13:31

Avitus

16k6 gold badges47 silver badges54 bronze badges

3 Comments

Iridium Over a year ago

A semicolon isn't required here. The actual issue looks to be the missing top which you've added (though not mentioned).

Avitus Over a year ago

In the OP there is no space so there needs to be a delimiter for the parser. I have updated answer to include keyword top

Iridium Over a year ago

Yes there are - It's a verbatim string so the text will include all the newlines as seen in the code (as well as all the spaces used to indent the lines from the 2nd onwards).

Daniel Cacheta · Accepted Answer · 2018-01-19 13:43:04Z

-1

You'll probably want to use CommandText to define the query and Parameters property to bind the expected filter to the query. I believe this example can be useful: https://msdn.microsoft.com/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx#Anchor_2

answered Jan 19, 2018 at 13:43

Daniel Cacheta

441 silver badge4 bronze badges

Collectives™ on Stack Overflow

C# SqlCommand command with parameter in the middle of query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related