Non-string paramaters cause an error.

[pacman82]

Non-string paramaters cause an error.

[pacman82]

When you are sending all paramaters as strings, is the database still binding paramaters? If you are instead concatenating strings to generate an SQL statement that includes paramater values, these methods would be vulneratble to SQL injection.

These are bound VARCHAR parameters. arrow-odbc does not perform string concatination and does not introduce vulnerability to SQL injection.

Definitely don't convert a python float to an SQL decimal, since one is base 2 and the other is base 10.

SQL_DECIMAL is a relational type. It does not specify a representation. Multiple C-Types can be used to transfer SQL_DECIMAL.

I'm not sure why they picked SQL_NUMERIC instead of SQL_DECIMAL for python decimal.Decimal. Those two ODBC types look like aliases for the same type?

SQL_NUMERIC and SQL_DECIMAL differ in the range of values they support.

[pacman82]

I'll give your suggestions some thought. With my current workload it is hypothetical anyway. The better error message must suffice for now. Thanks for alerting me to the issue.

[pacman82]

Hello @rosscoleman ,

There's some overhead if the database has to cast types when binding paramaters, so I think it would be best to support Python ints, floats, and dates.

I am avoiding this as of now, as there is no clear mapping between Python and ODBC types. Should a float be converted to a double or a Decimal? Without inspecting the relational parameter type first, it is not possible to make a good decision in the genereal case. There is also not every conversion supported between ODBC types and Relational types on the database. String works everythere except for binary data.

I think you should add a more descriptive error message that only string query parameters are allowed

Good idea. Please enjoy arrow-odbc v1.2.4. Which shoud provide a better error messae.

[rosscoleman]

When you are sending all paramaters as strings, is the database still binding paramaters? If you are instead concatenating strings to generate an SQL statement that includes paramater values, these methods would be vulneratble to SQL injection. If so, your API is really breaking my expectations about security. I've only ever used JDBC and pyodbc, and not ODBC from C or C++, but I wonder about this because JDBC drivers can throw errors if you send the wrong data types for bound parameters.

---

As for type conversions, I think pyodbc made sensible decisions. Definitely don't convert a python float to an SQL decimal, since one is base 2 and the other is base 10. I'm not sure why they picked SQL_NUMERIC instead of SQL_DECIMAL for python decimal.Decimal. Those two ODBC types look like aliases for the same type?

https://github.com/mkleehammer/pyodbc/wiki/Data-Types

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/sql-data-types?view=sql-server-ver16