|
1
24
25 package org.objectweb.cjdbc.scenario.users;
26
27 import java.sql.Connection  ;
28 import java.sql.DriverManager ;
29 import java.sql.ResultSet ;
30 import java.sql.SQLException ;
31 import java.sql.Statement ;
32 import java.util.ArrayList ;
33 import java.util.Arrays ;
34 import java.util.HashMap ;
35 import java.util.Iterator ;
36 import java.util.TreeSet ;
37
38 import org.apache.log4j.FileAppender;
39 import org.apache.log4j.Level;
40 import org.apache.log4j.Logger;
41 import org.apache.log4j.SimpleLayout;
42 import org.objectweb.cjdbc.scenario.templates.SQLInjectionTemplate;
43 import org.objectweb.cjdbc.scenario.tools.ScenarioUtility;
44
45
52 public final class SQLInjectionScenario extends SQLInjectionTemplate
53 {
54
55 static final String LOG_FILE = "sqlinjection.txt";
56
57 static final String [] URLS = (urls != null)
58 ? urls
59 : new String []{
60 "jdbc:hsqldb:hsql://localhost:9001", "jdbc:hsqldb:hsql://localhost:9002"};
61 static final String [] USERS = (users != null)
62 ? users
63 : new String []{"test",
64 "test" };
65 static final String [] PASSWORDS = (passwords != null)
66 ? passwords
67 : new String []{"", ""};
68 static final String [] CLASSES = (classes != null)
69 ? classes
70 : new String []{
71 "org.hsqldb.jdbcDriver", "org.hsqldb.jdbcDriver" };
72
73 static final String CJDBC_DRIVER = "org.objectweb.cjdbc.driver.Driver";
74 static final String CJDBC_URL = (cjdbcurl != null)
75 ? cjdbcurl
76 : "jdbc:cjdbc://localhost/myDB";
77 static final String CJDBC_USER = (cjdbcuser != null)
78 ? cjdbcuser
79 : "user";
80 static final String CJDBC_PASSWORD = (cjdbcpassword != null)
81 ? cjdbcpassword
82 : "";
83
84 static final int THREAD_COUNT = (threadcount != null)
85 ? Integer
86 .parseInt(threadcount)
87 : 400;
88 static final int THREAD_START_WAIT_TIME_RANGE = (threadstartwaittimerange != null)
89 ? Integer
90 .parseInt(threadstartwaittimerange)
91 : 100;
92 static final int UNIT_RUN_COUNT = (unitruncount != null)
93 ? Integer
94 .parseInt(unitruncount)
95 : 10;
96 static final int THREAD_WAIT_TIME = (threadwaittime != null)
97 ? Integer
98 .parseInt(threadwaittime)
99 : 50;
100 static final int JOIN_THREAD_TIMEOUT = (jointhreadtimeout != null)
101 ? Integer
102 .parseInt(jointhreadtimeout)
103 : 100000;
104
105 static final boolean DROP_TABLES = (dropTables != null)
106 ? Boolean.valueOf(
107 dropTables)
108 .booleanValue()
109 : true;
110
111 static final boolean CREATE_TABLES = (createTables != null)
112 ? Boolean.valueOf(
113 createTables)
114 .booleanValue()
115 : true;
116
117 static final String SQLgameTrans = "INSERT INTO GAMETRANSACTION (transactionId, gameId, customerId) VALUES (?,1,1)";
118 static final String SQLgame = "INSERT INTO GAME (gameId, transactionId, gameType, gameAmount) VALUES (?,?,1,1)";
119 static final String SQLlotto = "INSERT INTO LOTTERY (lotteryId, gameId, NUMBER1, NUMBER2, NUMBER3) VALUES(?,?,1,1,1)";
120
121 static final String SQLgameTransMax = "Select max(transactionId) FROM GAMETRANSACTION";
122 static final String SQLgameTransCount = "Select count(transactionId) FROM GAMETRANSACTION";
123 static final String SQLgameMax = "Select max(gameId) from GAME";
124 static final String SQLgameCount = "Select count(gameId) from GAME";
125 static final String SQLlottoMax = "Select max(lotteryId) from LOTTERY";
126 static final String SQLlottoCount = "Select count(lotteryId) from LOTTERY";
127
128 static final String createGameTransaction = "CREATE TABLE GAMETRANSACTION (transactionId INTEGER NOT NULL PRIMARY KEY,gameId INTEGER NOT NULL,customerId INTEGER NOT NULL)";
129 static final String createGame = "CREATE TABLE GAME (gameId INTEGER NOT NULL PRIMARY KEY,transactionId INTEGER NOT NULL,gameType INTEGER NOT NULL,gameAmount INTEGER NOT NULL)";
130 static final String createLottery = "CREATE TABLE LOTTERY (lotteryId INTEGER NOT NULL PRIMARY KEY,gameId INTEGER NOT NULL,NUMBER1 INTEGER NOT NULL,NUMBER2 INTEGER NOT NULL,NUMBER3 INTEGER NOT NULL)";
131
132 static Logger logger;
133 static int key = (keyIndex != null)
134 ? Integer
135 .parseInt(keyIndex)
136 : 0;
137 static Object synchObj = new Object ();
138
139 static
140 {
141
142 try
143 {
144 logger = Logger.getLogger(SQLInjectionTest.class);
145 FileAppender fileappender = new FileAppender(new SimpleLayout(),
146 LOG_FILE, true);
147 fileappender.setImmediateFlush(true);
148 logger.addAppender(fileappender);
149 logger.setLevel(Level.INFO);
150 }
151 catch (Exception e)
152 {
153 System.exit(0);
154 }
155 try
156 {
157 Class.forName(CJDBC_DRIVER);
158 }
159 catch (Exception e)
160 {
161 logger.error("Unable to load cjdbc driver " + e.toString());
162 fail("Unable to load cjdbc driver " + e.toString());
163 }
164 }
165
166
169 public Connection getBackendConnection(int index) throws Exception
170 {
171 Class.forName(CLASSES[index]);
172 logger.debug("Connecting to:" + URLS[index] + ";" + USERS[index] + ";"
173 + PASSWORDS[index]);
174 return DriverManager.getConnection(URLS[index], USERS[index],
175 PASSWORDS[index]);
176 }
177
178
181 public void testSQLInjection()
182 {
183
184 logger.info("Starting sql injection test");
185 if (standaloneTest)
186 logger.info("The test has started an internal controller");
187 else
188 logger.info("The test is connected to cjdbc url:" + cjdbcurl);
189
190 logger.info("The following parameters are used:");
191 logger.info("CJDBC_URL:" + CJDBC_URL);
192 logger.info("CJDBC_USER:" + CJDBC_USER);
193 logger.info("CJDBC_PASSWORD:" + CJDBC_PASSWORD);
194 logger.info("BACKENDS_URLS:" + Arrays.asList(URLS));
195 logger.info("BACKENDS_USERS:" + Arrays.asList(USERS));
196 logger.info("BACKENDS_PASSWORDS:" + Arrays.asList(PASSWORDS));
197 logger.info("THREAD_COUNT:" + THREAD_COUNT);
198 logger.info("THREAD_START_WAIT_TIME_RANGE:" + THREAD_START_WAIT_TIME_RANGE);
199 logger.info("UNIT_RUN_COUNT:" + UNIT_RUN_COUNT);
200 logger.info("THREAD_WAIT_TIME:" + THREAD_WAIT_TIME);
201 logger.info("JOIN_THREAD_TIMEOUT:" + JOIN_THREAD_TIMEOUT);
202 logger.info("CREATE_TABLE:" + CREATE_TABLES);
203 logger.info("DROP_TABLES:" + DROP_TABLES);
204 logger.info("KEY_INDEX:" + key);
205
206 Connection con = null;
207
208 if (DROP_TABLES)
209 {
210 logger.info("Dropping tables...");
211 for (int i = 0; i < URLS.length; i++)
212 {
213 try
214 {
215 con = getBackendConnection(i);
216 ResultSet tables = con.getMetaData().getTables(null, null, "%",
217 new String []{"TABLE"});
218 while (tables.next())
219 {
220 String table = tables.getString("TABLE_NAME");
221 logger.info("Found table:" + table);
222 if (table.equalsIgnoreCase("GAMETRANSACTION")
223 || table.equalsIgnoreCase("GAME")
224 || table.equalsIgnoreCase("LOTTERY"))
225 {
226 logger.info("Dropping table " + table);
227 con.createStatement().executeUpdate("DROP TABLE " + table);
228 }
229 }
230 }
231 catch (Exception e)
232 {
233 e.printStackTrace();
234 }
236 }
237 }
238
239 if (CREATE_TABLES)
240 {
241 logger.info("Creating tables...");
242 for (int i = 0; i < URLS.length; i++)
243 {
244 try
245 {
246 con = getBackendConnection(i);
247 con.createStatement().executeUpdate(createGameTransaction);
248 con.createStatement().executeUpdate(createGame);
249 con.createStatement().executeUpdate(createLottery);
250 }
251 catch (Exception e)
252 {
253 e.printStackTrace();
254 }
256 }
257
258 }
259
260 try
261 {
262 logger.info("Creating connection...");
263 con = getConnection();
264 }
265 catch (Exception e)
266 {
267 fail("Cannot connect to cjdbc:" + e.getMessage());
268 }
269
270 try
271 {
272 logger.info("Taking my breath before test...");
273 Thread.sleep(2000);
274 }
275 catch (InterruptedException e2)
276 {
277 }
278
279 Thread [] threads = new Thread [THREAD_COUNT];
280
281 for (int i = 0; i < THREAD_COUNT; i++)
282 {
283 threads[i] = new Thread (new SQLInjectionTest());
284 threads[i].start();
285
286 try
287 {
288 Thread.sleep((int) (Math.random() * THREAD_START_WAIT_TIME_RANGE));
289 }
290 catch (Exception e)
291 {
292 }
293 }
294
295 for (int i = 0; i < THREAD_COUNT; i++)
296 {
297 try
298 {
299 if (threads[i].isAlive())
300 {
301 threads[i].join();
302 }
303 }
304 catch (InterruptedException e1)
305 {
306 e1.printStackTrace();
307 }
308 }
309
310 try
311 {
312
313 Connection cjdbc = getCJDBCConnection();
314 Integer [] result3 = getIds(cjdbc);
315 displayResult(result3, cjdbcurl);
316
317 if (!standaloneTest)
318 {
319 logger.info("Checking following urls:" + Arrays.asList(urls));
320 for (int i = 0; i < urls.length; i++)
321 {
322 Integer [] result = getIds(getBackendConnection(i));
323 displayResult(result, urls[i]);
324
325 assertEquals("Inconsistent result", Arrays.asList(result), Arrays
326 .asList(result3));
327 }
328 }
329 else
330 {
331 for (int i = 0; i < URLS.length; i++)
332 {
333 Connection backend = getBackendConnection(i);
334 Integer [] result1 = getIds(backend);
335 displayResult(result1, URLS[i]);
336 }
337 }
342 }
343 catch (Exception e)
344 {
345 e.printStackTrace();
346 fail("Verification failed:" + e.getMessage());
347 }
348 }
349
350 private static final void displayResult(Integer [] res, String string)
351 {
352 logger.info("=========" + string + "========");
353 for (int i = 0; i < res.length; i++)
354 logger.info("RES[" + i + "]=" + res[i]);
355 logger.info("========================");
356 }
357
358 private static final Integer [] getIds(Connection con) throws Exception
359 {
360 ArrayList list1 = ScenarioUtility
361 .getSingleQueryResult(SQLgameTransMax, con);
362 ArrayList list2 = ScenarioUtility.getSingleQueryResult(SQLgameMax, con);
363 ArrayList list3 = ScenarioUtility.getSingleQueryResult(SQLlottoMax, con);
364 ArrayList list4 = ScenarioUtility.getSingleQueryResult(SQLgameTransCount,
365 con);
366 ArrayList list5 = ScenarioUtility.getSingleQueryResult(SQLgameCount, con);
367 ArrayList list6 = ScenarioUtility.getSingleQueryResult(SQLlottoCount, con);
368 return new Integer []{getId(list1), getId(list2), getId(list3),
369 getId(list4), getId(list5), getId(list6)};
370 }
371
372 private static final Integer getId(ArrayList list)
373 {
374 String o = (String ) ((ArrayList ) list.get(0)).get(0);
375 return new Integer (o);
376 }
377
378
383 public static synchronized int getKey()
384 {
385 synchronized (synchObj)
386 {
387 key = key + 1;
388 return key;
389 }
390 }
391
392
398 class SQLInjectionTest implements Runnable
399 {
400 private StringBuffer sqlGameTransBuf;
401 private StringBuffer sqlGameBuf;
402 private StringBuffer sqlLottoBuf;
403 private StringBuffer buf;
404 Connection con = null;
405
406
409 public void run()
410 {
411 for (int i = 0; i < UNIT_RUN_COUNT; i++)
412 {
413 try
414 {
415 setUp();
416 testCJDBCSqlInjection();
417 tearDown();
418
419 try
420 {
421 synchronized (this)
422 {
423 wait((int) (Math.random() * THREAD_WAIT_TIME) + 1);
424 }
425 }
426 catch (Exception e)
427 {
428 }
429 }
430 catch (Exception e)
431 {
432 fail("Error in thread:" + e.toString());
433 logger.error("" + e.toString());
434 }
435 finally
436 {
437 try
438 {
439 tearDown();
440 }
441 catch (Exception e)
442 {
443 logger.error("Unable to close connection after bad run: "
444 + e.toString());
445 }
446 }
447 }
448 }
449
450
455 public void setUp() throws Exception
456 {
457 con = null;
458 SQLUtil s = new SQLUtil();
459 logger = Logger.getLogger(SQLInjectionTest.class);
460 try
461 {
462 con = DriverManager
463 .getConnection(CJDBC_URL, CJDBC_USER, CJDBC_PASSWORD);
464 }
465 catch (Exception se)
466 {
467 se.printStackTrace();
468 logger.error(se.toString());
469 con = null;
470 throw se;
471 }
472
473 int localKey = getKey();
474
475 buf = new StringBuffer ("\nTRANSKEY: ");
476 buf.append(localKey).append("\n");
477
478 s.clearParams();
480 s.clearSQL();
481 s.setSQL(SQLgameTrans);
483 s.setInt(1, localKey);
484 sqlGameTransBuf = s.prepareSQL();
485
486 s.clearParams();
487 s.clearSQL();
488 s.setSQL(SQLgame);
489 s.setInt(1, localKey);
490 s.setInt(2, localKey);
491 sqlGameBuf = s.prepareSQL();
492
493 s.clearParams();
494 s.clearSQL();
495 s.setSQL(SQLlotto);
496 s.setInt(1, localKey);
497 s.setInt(2, localKey);
498 sqlLottoBuf = s.prepareSQL();
499
500 }
501
502
507 public void tearDown() throws Exception
508 {
509 if (con != null)
510 {
511 con.close();
512 con = null;
513 }
514 }
515
516
519 public void testCJDBCSqlInjection()
520 {
521 if (con == null)
522 {
523 logger.error("Connection is NULL");
524 }
525 else
526 {
527 buf.append("\n---------SQL Statments created-------\n").append(
528 sqlGameTransBuf.toString()).append("\n");
529 buf.append(sqlGameBuf.toString()).append("\n").append(
530 sqlLottoBuf.toString());
531 buf.append("\n---------SQL Statments end -------\n\n");
532 try
533 {
534 logger.debug(buf.toString());
535
536 con.setAutoCommit(false);
537
538 Statement stmt = con.createStatement();
539 try
540 {
541 stmt.execute(sqlGameTransBuf.toString());
542 }
543 catch (Exception e)
544 {
545 e.printStackTrace();
546 }
547 finally
548 {
549 stmt.close();
550 }
551
552 stmt = con.createStatement();
553 try
554 {
555 stmt.execute(sqlGameBuf.toString());
556 }
557 catch (Exception e)
558 {
559 e.printStackTrace();
560 }
561 finally
562 {
563 stmt.close();
564 }
565
566 stmt = con.createStatement();
567 try
568 {
569 stmt.execute(sqlLottoBuf.toString());
570 }
571 catch (Exception e)
572 {
573 e.printStackTrace();
574 }
575 finally
576 {
577 stmt.close();
578 }
579
580 con.commit();
581
586
587 }
589 catch (SQLException se)
590 {
591 logger.warn("Commit Failure: " + se);
592 try
593 {
594 con.rollback();
595 }
596 catch (SQLException s)
597 {
598 logger.error("RollBack Failure: " + s);
599 }
600 }
601 }
602 }
603 }
604
605
608 class SQLUtil
609 {
610 private Connection connection = null;
611 private String sql = "";
612 private HashMap params = new HashMap ();
613
614
618 public SQLUtil()
619 {
620 }
621
622 public void setInt(int index, int value) throws SQLException
623 {
624 params.put(new Integer (index), "" + value);
625 }
626
627 public void clearParams()
628 {
629 params.clear();
630 }
631
632 public void setSQL(String sql)
633 {
634 this.sql = sql;
635 }
636
637 public String getSQL(boolean withParams)
638 {
639 try
640 {
641 if (withParams)
642 {
643 StringBuffer builder = new StringBuffer ();
644 String [] tokens = sql.split("\\x3f");
645 int tokenIterator = 0;
646 Iterator i = new TreeSet (params.keySet()).iterator();
647 while (i.hasNext())
648 {
649 builder.append(tokens[tokenIterator++]);
650 builder.append(params.get(i.next()));
651 }
652 if (tokenIterator < tokens.length)
653 {
654 builder.append(tokens[tokenIterator++]);
655 }
656 return builder.toString();
657 }
658 return this.sql;
659 }
660 catch (Exception e)
661 {
662 e.printStackTrace();
663 return null;
664 }
665 }
666
667 public void clearSQL()
668 {
669 setSQL("");
670 }
671
672 public Connection getConnection() throws SQLException
673 {
674 return connection;
675 }
676
677 public StringBuffer prepareSQL()
678 {
679 try
680 {
681 StringBuffer builder = new StringBuffer ();
682 String [] tokens = sql.split("\\x3f");
683 int tokenIterator = 0;
684 Iterator i = new TreeSet (params.keySet()).iterator();
685 while (i.hasNext())
686 {
687 builder.append(tokens[tokenIterator++]);
688 builder.append(params.get(i.next()));
689 }
690 if (tokenIterator < tokens.length)
691 {
692 builder.append(tokens[tokenIterator++]);
693 }
694 return builder;
695 }
696 catch (Exception e)
697 {
698 System.err.println(e.toString());
699 }
700 finally
701 {
702 clearParams();
703 clearSQL();
704 }
705 return null;
706 }
707 }
708 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.