locked
Need help inserting parameter with additional strings RRS feed

  • Question

  • User126014556 posted

    How can I insert the parameter plus additional string into TSQL

    Dim query As String = "INSERT INTO Rep_Links(Module_Code, Rep_Code, Rep_Name, Rep_Web_Link) " &
            "VALUES (@Module_Code, (SELECT TOP 1 [Module_Code] + replicate('0', 3 - len(MAX(CAST(RIGHT([Rep_Code],3) AS INT)) + 1)) + CAST(MAX(CAST(RIGHT([Rep_Code],3) AS INT)) + 1 AS VARCHAR) FROM [Rep_Links] WHERE [Module_Code] = @Module_Code GROUP BY [Module_Code])," &
    " <A HREF="@Rep_Web_Link" target="_blank">@Rep_Name</A> "

    The issue is with the below line, as when I try to insert the two parameters @Rep_Web_Link and @Rep_Name into Rep_Web_Link column.

    <A HREF="@Rep_Web_Link" target="_blank">@Rep_Name</A>

    I would like the record to be inserted into the Rep_Web_Link column as in the following result for example:

    <A HREF="http://sample.com" target="_blank">sample page</A>

    Saturday, August 3, 2019 11:55 AM

Answers

  • User126014556 posted

    I figured it out that the closing  bracket was missing plus additional changes for the double quotes.

            Dim query As String = "INSERT INTO Rep_Links(Module_Code, Rep_Code, Rep_Name, Rep_Web_Link) " &
            "VALUES (@Module_Code, (SELECT TOP 1 [Module_Code] + replicate('0', 3 - len(MAX(CAST(RIGHT([Rep_Code],3) AS INT)) + 1)) + CAST(MAX(CAST(RIGHT([Rep_Code],3) AS INT)) + 1 AS VARCHAR) FROM [Rep_Links] WHERE [Module_Code] = @Module_Code GROUP BY [Module_Code])," &
            " @Rep_Name, '<A HREF=""' + @Rep_Web_Link + '"" target=""_blank"">' + @Rep_Name + '</A>')"

    I appreciate if someone can tell me if the above tsql statement is vulnerable to sql injection. If so how can I improve the sql statement.

    Thanks in advance

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 3, 2019 12:08 PM