Databricks Simba Spark ODBC .NET8 C# Driver Parameters in SQL Queries

Hi @Luigi Navarra,

From the error message, we can find limit and OFFSET are not bound. So you can try the code below and tell us the test result.

using System.Data.Odbc;
public void QueryDatabase(string connectionString, string param1value, int param2value, int limit, int offset)
{
    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        connection.Open();
        using (OdbcCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
                SELECT * 
                FROM MyTable
                WHERE param1 = ? 
                AND param2 = ?
                LIMIT ? OFFSET ?";
            cmd.Parameters.Add(new OdbcParameter("@param1", OdbcType.Text)).Value = param1value;
            cmd.Parameters.Add(new OdbcParameter("@param2", OdbcType.Int)).Value = param2value;
            cmd.Parameters.Add(new OdbcParameter("@limit", OdbcType.Int)).Value = limit;
            cmd.Parameters.Add(new OdbcParameter("@offset", OdbcType.Int)).Value = offset;
            using (OdbcDataReader reader = cmd.ExecuteReader())
            {
                // ...
            }
        }
        connection.Close();
    }
}

Best Regards

Jason

Hi Jason,

the wildcard ? does not work with LIMIT and OFFSET, it returns a syntax error, may be it is a limitation of the Sql92 dialect of the ODBC driver.
By the way I tried to remove the LIMIT/OFFSET clauses and I got the query correctly executed. With few tentatives more I found that the problem is the OFFSET only.

If i use OFFESET in a query with ? it does not work and I get the [UNBOUND_SQL_PARAMETER] excepetion. Without OFFSET the query works fine.

    using (OdbcConnection connection = (OdbcConnection)dbConnectionFactory.GetConnection(dbConfig.ConnectionString))
    {
        connection.Open();
        using (OdbcCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = $@"
SELECT * 
FROM MyTable WHERE param1 = ? AND param2= ?
   LIMIT 100";
            cmd.Parameters.Add(new OdbcParameter("param1", OdbcType.Text)).Value = param.param1val;
            cmd.Parameters.Add(new OdbcParameter("param2", OdbcType.Int)).Value = param.param2val;
            OdbcDataReader reader = cmd.ExecuteReader();
            connection.Close();
        }
    }

Avoiding the use of the wildcard, but using only string interpolation, the query is correclty executed (i wonder if the odbc driver passes only the plain text and sql dialect issue may not happen).

I need to parametrize the query but also build complex queries and use clauses like LIMIT and OFFSET. So i think i will use use the string interpolation and use some .net library to validate data and to be safe from sql injection.

Using plain text string I'm also able to use Dapper ORM over the ODBC driver.
Thanks for the help

@Luigi Navarra - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

@PRADEEPCHEEKATLA-MSFT i'm using plain text string in order to user Dapper. This means that any parameter in the query string is passed using string interpolation. To prevent sql injection i user LightVx nuget package that validated inputs that may contain potential risks.

@Luigi Navarra - Thank you for letting me know. It's great to hear that you are using Dapper ORM and LightVx NuGet package to prevent SQL injection attacks.

Dapper is a lightweight ORM that provides a simple way to map database results to objects. It can help you write cleaner and more maintainable code. LightVx is a NuGet package that provides input validation and sanitization to prevent SQL injection attacks and other security vulnerabilities.

By using Dapper and LightVx together, you can ensure that your application is secure and protected against SQL injection attacks. It's always important to validate and sanitize user input to prevent security vulnerabilities, and it's great to see that you are taking the necessary steps to do so.

If you have any further questions or concerns, please don't hesitate to ask.