1 /*
2 * Test conversion of parameter values from C to SQL datatypes.
3 */
4 #include <stdio.h>
5 #include <stdlib.h>
6
7 #include "common.h"
8
9 #define TEST_CONVERT(sql, c_type, sql_type, value) \
10 test_convert(sql, c_type, #c_type, sql_type, #sql_type, value)
11
12 static void test_convert(const char *sql,
13 SQLSMALLINT c_type, const char *c_type_str,
14 SQLSMALLINT sql_type, const char *sql_type_str,
15 SQLPOINTER value);
16
17 static HSTMT hstmt = SQL_NULL_HSTMT;
18
main(int argc,char ** argv)19 int main(int argc, char **argv)
20 {
21 SQLRETURN rc;
22 SQLINTEGER intparam;
23 char byteaparam[] = { 'f', 'o', 'o', '\n', '\\', 'b', 'a', 'r', '\0' };
24
25 /*
26 * let's not confuse the output with LF conversions. There's a separate
27 * regression test for that.
28 */
29 test_connect_ext("LFConversion=0");
30
31 rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
32 if (!SQL_SUCCEEDED(rc))
33 {
34 print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
35 exit(1);
36 }
37
38 /*** Test proper escaping of integer parameters ***/
39 printf("\nTesting conversions...\n");
40
41 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_INTEGER, "2");
42 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_INTEGER, "-2");
43 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "2");
44 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "-2");
45 TEST_CONVERT("SELECT 2.2 > ?", SQL_C_CHAR, SQL_FLOAT, "2.3");
46 TEST_CONVERT("SELECT 3.3 > ?", SQL_C_CHAR, SQL_DOUBLE, "3.01");
47 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_CHAR, "5 escapes: \\ and '");
48
49 /* test boundary cases */
50 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "32767");
51 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "-32768");
52
53 /*
54 * The result of these depend on whether the server treats the parameters
55 * as a string or an integer.
56 */
57 printf("\nTesting conversions whose result depend on whether the\n");
58 printf("parameter is treated as a string or an integer...\n");
59 TEST_CONVERT("SELECT '555' > ?", SQL_C_CHAR, SQL_INTEGER, "6");
60 TEST_CONVERT("SELECT '555' > ?", SQL_C_CHAR, SQL_SMALLINT, "6");
61 TEST_CONVERT("SELECT '555' > ?", SQL_C_CHAR, SQL_CHAR, "6");
62
63 /*
64 * The result of this test depends on what datatype the server thinks
65 * it's dealing with. If the driver sends it as a naked literal, the
66 * server will treat it as a numeric because it doesn't fit in an int4.
67 * But if the driver tells the server what the datatype is, int4, the
68 * server will throw an error. In either case, this isn't something that
69 * a correct application should be doing, because it's clearly not a
70 * valid value for an SQL_INTEGER. But it's an interesting edge case to
71 * test.
72 */
73 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_INTEGER, "99999999999999999999999");
74
75 printf("\nTesting conversions with invalid values...\n");
76
77 TEST_CONVERT("SELECT 2 > ?", SQL_C_CHAR, SQL_INTEGER, "2, 'injected, BAD!'");
78 TEST_CONVERT("SELECT 2 > ?", SQL_C_CHAR, SQL_SMALLINT, "2, 'injected, BAD!'");
79 TEST_CONVERT("SELECT 1.3 > ?", SQL_C_CHAR, SQL_FLOAT, "3', 'injected, BAD!', '1");
80 TEST_CONVERT("SELECT 1.4 > ?", SQL_C_CHAR, SQL_FLOAT, "4 \\'bad', '1");
81 TEST_CONVERT("SELECT 1-?", SQL_C_CHAR, SQL_INTEGER, "-1");
82 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_INTEGER, "-");
83 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_INTEGER, "");
84 TEST_CONVERT("SELECT 1-?", SQL_C_CHAR, SQL_SMALLINT, "-1");
85 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "-");
86 TEST_CONVERT("SELECT 1 > ?", SQL_C_CHAR, SQL_SMALLINT, "");
87
88 intparam = 1234;
89 TEST_CONVERT("SELECT 0-?", SQL_C_SLONG, SQL_INTEGER, &intparam);
90 intparam = -1234;
91 TEST_CONVERT("SELECT 0-?", SQL_C_SLONG, SQL_INTEGER, &intparam);
92
93 intparam = 1234;
94 TEST_CONVERT("SELECT 0-?", SQL_C_SLONG, SQL_SMALLINT, &intparam);
95 intparam = -1234;
96 TEST_CONVERT("SELECT 0-?", SQL_C_SLONG, SQL_SMALLINT, &intparam);
97
98 printf("\nTesting bytea conversions\n");
99 TEST_CONVERT("SELECT ?", SQL_C_BINARY, SQL_BINARY, byteaparam);
100 TEST_CONVERT("SELECT ?", SQL_C_CHAR, SQL_BINARY, "666f6f0001");
101 TEST_CONVERT("SELECT ?::text", SQL_C_BINARY, SQL_CHAR, byteaparam);
102
103 printf("\nTesting datetime conversions\n");
104 TEST_CONVERT("SELECT ?", SQL_C_CHAR, SQL_TIMESTAMP, "04-22-2011 01:23:45");
105 TEST_CONVERT("SELECT ?", SQL_C_CHAR, SQL_TIMESTAMP, "{ts '2011-04-22 01:23:45'}");
106 TEST_CONVERT("SELECT ?", SQL_C_CHAR, SQL_TIME, "{t '01:23:45'}");
107 TEST_CONVERT("SELECT ?", SQL_C_CHAR, SQL_DATE, "{d '2011-04-22'}");
108
109 /* Clean up */
110 test_disconnect();
111
112 return 0;
113 }
114
115 /*
116 * Execute a query with one parameter, with given C and SQL types. Print
117 * error or result.
118 */
119 static void
test_convert(const char * sql,SQLSMALLINT c_type,const char * c_type_str,SQLSMALLINT sql_type,const char * sql_type_str,SQLPOINTER value)120 test_convert(const char *sql,
121 SQLSMALLINT c_type, const char *c_type_str,
122 SQLSMALLINT sql_type, const char *sql_type_str,
123 SQLPOINTER value)
124 {
125 SQLRETURN rc;
126 SQLLEN cbParam = SQL_NTS;
127 int failed = 0;
128
129 /* Print what we're doing */
130 switch (c_type)
131 {
132 case SQL_C_SLONG:
133 printf("Testing \"%s\" with %s -> %s param %d...\n",
134 sql, c_type_str, sql_type_str, *((SQLINTEGER *) value));
135 break;
136
137 case SQL_C_CHAR:
138 printf("Testing \"%s\" with %s -> %s param \"%s\"...\n",
139 sql, c_type_str, sql_type_str, (char *) value);
140 break;
141
142 default:
143 printf("Testing \"%s\" with %s -> %s param...\n",
144 sql, c_type_str, sql_type_str);
145 break;
146 }
147
148 if (c_type == SQL_BINARY)
149 cbParam = strlen(value) + 1;
150 else
151 cbParam = SQL_NTS; /* ignored for non-character data */
152
153 rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
154 c_type, /* value type */
155 sql_type, /* param type */
156 20, /* column size */
157 0, /* dec digits */
158 value, /* param value ptr */
159 0, /* buffer len */
160 &cbParam /* StrLen_or_IndPtr */);
161 CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
162
163 rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);
164 if (!SQL_SUCCEEDED(rc))
165 {
166 print_diag("SQLExecDirect failed", SQL_HANDLE_STMT, hstmt);
167 failed = 1;
168 }
169 else
170 print_result(hstmt);
171
172 rc = SQLFreeStmt(hstmt, SQL_CLOSE);
173 CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
174
175 /*
176 * In error_on_rollback=0 mode, we don't currently recover from the error.
177 * I think that's a bug in the driver, but meanwhile, let's just force
178 * a rollback manually
179 */
180 if (failed)
181 {
182 rc = SQLExecDirect(hstmt, (SQLCHAR *) "ROLLBACK /* clean up after failed test */", SQL_NTS);
183 CHECK_STMT_RESULT(rc, "SQLExecDirect(ROLLBACK) failed", hstmt);
184 }
185
186 printf("\n");
187 }
188
189