Bug #34575 SQL_C_CHAR value type and numeric parameter type causes trouble
Submitted: 15 Feb 2008 1:12 Modified: 14 Mar 2008 18:37
Reporter: Jim Winstead Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51 OS:Any
Assigned to: Jim Winstead CPU Architecture:Any

[15 Feb 2008 1:12] Jim Winstead 
Description:
If a parameter with a value type of SQL_C_CHAR, but the parameter type that is numeric (such as SQL_DECIMAL or SQL_INTEGER), and the length of the parameter value is 0, the parameter marker is simply stripped from the query, leaving a probably-invalid query.

Additionally, if the parameter value has a non-zero length but is more than just numeric, that value is inserted as-is into the query, leading to possible SQL injection problems.

How to repeat:
/**
 Bug #34575: SQL_C_CHAR value type and numeric parameter type causes trouble
*/
DECLARE_TEST(t_bug34575)
{
  SQLCHAR buff[10];
  SQLLEN len= 0;
  SQLSMALLINT namelen, type, digits, nullable;

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug34575");
  ok_sql(hstmt, "CREATE TABLE t_bug34575 (a DECIMAL)");
  ok_sql(hstmt, "INSERT INTO t_bug34575 VALUES (1.0),(2.0),(3.0)");

  ok_stmt(hstmt, SQLPrepare(hstmt, (SQLCHAR *)
                            "SELECT a FROM t_bug34575 WHERE a = ?", SQL_NTS));
  strcpy((char *)buff, "2.0");
  ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
                                  SQL_DECIMAL, 10, 0, buff, sizeof(buff),
                                  &len));

  ok_stmt(hstmt, SQLDescribeCol(hstmt, 1, buff, sizeof(buff), &namelen,
                                &type, &len, &digits, &nullable));

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));

  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug34575");
  return OK;
}

Suggested fix:
Always quote and escape data provided as SQL_C_CHAR.
[15 Feb 2008 1:14] Jim Winstead 
Fix handling of SQL_C_CHAR being passed as numeric parameters

Attachment: bug34575.patch (text/plain), 5.22 KiB.
[13 Mar 2008 18:15] Jim Winstead 
Updated test

Attachment: bug34575.patch (text/plain), 5.22 KiB.
[13 Mar 2008 21:35] Jim Winstead 
The fix for this bug has been committed, and will be in 3.51.24.
[14 Mar 2008 18:37] MC Brown 
A note has been added to the 3.51.24 changelog: 

Security Enhancement: Accessing a parameer with the type of SQL_C_CHAR, but with a numeric type and a length of zero, the parameter marker would get stropped from the query. In addition, a SQL injection was possible if the parameter value had a non-zero length and was not numeric, the text would be inserted verbatim.