WebMethod - how to declare objects into sql consult string

Question

User17798125 posted

Hi forum, 

I have another doubt, I am trying to implement google charts, but when I tried to implement the method I found a little issue, I am selecting columns where another columns take the value from an object, in this case labels. For example: numpart = '" & Me.numpart.Text & "' , the console says that I cannot declare this in my method. 

Is there any form to do it? Because I need that info to pull the parts that I need. Thanks!!!

<WebMethod()>
    Public Shared Function GetChartData() As List(Of Object)
        Dim query As String = "SELECT inspT11, okT11, nokT11, retrT11 FROM stdplusreport1 WHERE numpart = '" & Me.numpart.Text & "' And emp = '" & Me.empresa.Text & "' AND rename = '" & Me.rename.Text & "'"
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim chartData As New List(Of Object)()
        chartData.Add(New Object() {"Issues", "Totales"})
        Using con As New SqlConnection(constr)
            Using cmd As New SqlCommand(query)
                cmd.CommandType = CommandType.Text
                cmd.Connection = con
                con.Open()
                Using sdr As SqlDataReader = cmd.ExecuteReader()
                    While sdr.Read()
                        chartData.Add(New Object() {sdr("Issues"), sdr("Totales")})
                    End While
                End Using
                con.Close()
                Return chartData
            End Using
        End Using
    End Function

Answer 1

User1120430333 posted

You'll want to prevent SQL injection attacks.

https://en.wikipedia.org/wiki/SQL_injection

The way you prevent the attack is to use parametrized T-SQL.

https://tableplus.io/blog/2018/08/best-practices-to-prevent-sql-injection-attacks.html

http://www.tutorialspanel.com/insert-update-delete-example-c-vb-net-using-executenonquery-method/

Also in using parametrized T-SQL,  it prevents malformed T-SQL, becuase of the way you are formulating the T-SQL with adding of parms in the T-SQL statement. What if one of the parm's data  had a single-quote, an escape sequence in T-SQL,  in the data? It would cause the T-SQL to be malformed and not work, becuase of the way you are formulating the T-SQL and not dealing with a single-quote coming in the text data..

It's just FYI below, but what you are doing to formulate the T-SQL, you are wide open to a SQL Injection attack.

http://net-informations.com/vbprj/ado.net/single-quotes.htm

Answer 2

User475983607 posted

I have another doubt, I am trying to implement google charts, but when I tried to implement the method I found a little issue, I am selecting columns where another columns take the value from an object, in this case labels. For example: numpart = '" & Me.numpart.Text & "' , the console says that I cannot declare this in my method. 

Web methods are shared and do not have access to class members like TextBoxes.  See the VB programming guide for language constructs.

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/shared

To solve this problem, pass the numpart, empresa, and rename values to the Web Method

GetChartData(string numpart, string empresa, string rename)

Answer 3

User753101303 posted

Hi,

A "page method" call is done out of band and doesn't go through all the ASPX page lifecycle (this is why it is shared ie it can't refer to an object instance as no page object is created for this call).

You'll need to pass those values as parameters to your GetChartData method and pass those values from your client side script. You are using PageMethods.GetChartData or jQuery ? See for example http://aspalliance.com/1922_PageMethods_In_ASPNET_AJAX.2

Not directly related but its best to use SQL parameters: https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Answer 4

User61956409 posted

Hi Jonsey10,

It seems that you'd like to get user input value(s) of TextBoxes in your WebMethod (a Shared Function), as mgebhard mentioned, you can modify your code to pass user input values as parameters, like below.

<WebMethod()>
Public Shared Function GetChartData(numpart As String, emp As String, rename As String) As List(Of Object)

    'your code logic here

    Return chartData
End Function

On JavaScript client side:

var numpart = $("#numpart").val();
var empresa = $("#empresa").val();
var rename = $("#rename").val();

var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
$.ajax({
    type: "POST",
    url: "TestPage.aspx/GetChartData",
    data: JSON.stringify(user_inputs),
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (data) {
        //your code logic here
        alert("success");

    }
});

Besides, instead of passing multiple parameters, you can modify your WebMethod to accept a custom object, like below.

<WebMethod()>
Public Shared Function GetChartData(user_inputs As UserInputs) As List(Of Object)

    'your code logic here

    Return chartData

End Function

On JavaScript client:

var numpart = $("#numpart").val();
var empresa = $("#empresa").val();
var rename = $("#rename").val();

var user_inputs = { "numpart": numpart, "emp": empresa, "rename": rename };
$.ajax({
    type: "POST",
    url: "WebForm1.aspx/GetChartData",
    //data: JSON.stringify(user_inputs),
    data: "{'user_inputs':" + JSON.stringify(user_inputs) + "}",
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (data) {
        //your code logic here
        alert("success");
    }
});

Test Result:

With Regards,

Fei Han