Bug #34575 SQL_C_CHAR value type and numeric parameter type causes trouble

• Submitted: 15 Feb 2008 2:12
• Modified: 14 Mar 2008 19:37
• Reporter: Jim Winstead
• Status: Closed
• Category: Connector/ODBC
• Severity: S3 (Non-critical)
• Version: 3.51
• OS: Any
• Assigned to: Jim Winstead
• Target Version: 3.51

[15 Feb 2008 2: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 2: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 19:15] Jim Winstead

Updated test

Attachment: bug34575.patch (text/plain), 5.22 KiB.

[13 Mar 2008 22:35] Jim Winstead

The fix for this bug has been committed, and will be in 3.51.24.

[14 Mar 2008 19: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.