none
C#> SQL2005 over webservice causes sqlparameter dbtype error RRS feed

  • Question

  • Hi,

    I am making an application which uses a database over the internet. The database can't be contacted directly so i thought setting up a layer with webservices. I am unsure if this is a good approach so suggestions are welcome!

    C# application ----- INTERNET ---- asp.net webservice ---- sql 2005 server

    The idea is that a webservice class calls the database and executes a query or stored procedure that is given from the application and then returns the dataset back to the application. I know it's safer to use only stored procedures to avoid sql injections, but this is only for testing purpose.

    so i have this webservice method:

    Code Snippet

            [WebMethod]
            public DataSet Query(string query, SqlParameter[] sqlparameters)
            {
                SqlConnection conn = new SqlConnection(connstring); ;
                DataSet returnDataset = new DataSet();
                SqlCommand command = new SqlCommand(query);
                try
                {
                    conn.Open();
                    command.Connection = conn;
                    if (sqlparameters != null)
                        command.Parameters.AddRange(sqlparameters);
                    IDataAdapter da = new SqlDataAdapter(command);
                    da.Fill(returnDataset);
                }
                catch (Exception ex)
                {
                    string s = ex.Message;
                }
                finally
                {
                    conn.Close();
                }
                return returnDataset;
            }


    When my program wants to execute a query he simply calls the webmethod

    Code Snippet

    Service service = new Service();
    service.Query(query, sqlparameters)



    The problem i have is that the sqlparameters that I pass mysteriously changes from string to Int 64. When the webservice tries to execute the query it throws an error:

    "Failed to convert parameter value from a String to a Int64."
    When i debug the code i also see that the structure of the sql parameter has somewhat changed . You can also clearly see that by passing the sqlparameters through the webservice have altered the dbtype. Here are the screenshots:

    Parameter when on client: http://demeersseman.be/screens/dbtypeclient.JPG
    Parameter when passed @ server http://demeersseman.be/screens/dbtypeserver.JPG

    Friday, August 10, 2007 8:02 AM

All replies

  • I would modify this web service. First of all it allows executing any type of SQL statements, which is already security vulnerability. Basically any outside caller could execute what they wish passing their own statements and parameters. I believe you need to create separate Web method per each database call you are required and pass only the values for the parameters, not actual parameters. Name of the stored procedure should be removed from the list of the parameters as well and used inside of the method explicitly. Too much flexibility will kill your application.

     

     

    Friday, August 10, 2007 10:27 AM
    Moderator
  • ok, i agree, but that won't solve the parameters issue..Sad
    Friday, August 10, 2007 11:56 AM
  • It will solve issue, because you will pass simple types in this case and they will be serialized/deserialized properly by the Web service. Your Web method calls should look like (assuming you need to get list of the customers)

     

    public DataSet GetCustomersQuery(string ParameterValue1, int ParameterValue2, .....)

    Friday, August 10, 2007 2:00 PM
    Moderator