SQLUtilsTest.java

package me.xhh.utils;

import junit.framework.TestCase;

import org.apache.commons.lang.StringEscapeUtils;

public class SQLUtilsTest extends TestCase {


	public void testFormatSql() {
		assertNull(SQLUtils.formatSQL(null));
		assertEquals("", SQLUtils.formatSQL("", "abc"));
		
		assertEquals("'no1' 'no2' 'no3'", SQLUtils.formatSQL("@ @ @", "no1", "no2", "no3"));
		assertEquals("no1 no2 no3", SQLUtils.formatSQL("@0 @0 @0", "no1", "no2", "no3"));
		assertEquals("no1 'no2' no3", SQLUtils.formatSQL("@0 @ @0", "no1", "no2", "no3"));
		assertEquals("'no1' 'no2' 'no3'", SQLUtils.formatSQL("@1 @2 @3", "no1", "no2", "no3"));
		assertEquals("no1 no2 no3", SQLUtils.formatSQL("@01 @02 @03", "no1", "no2", "no3"));
		assertEquals("no1 no2 no1", SQLUtils.formatSQL("@01 @02 @01", "no1", "no2", "no3"));
		assertEquals("no1 no2 no1", SQLUtils.formatSQL("@01 @02 @01", "no1", "no2"));
		assertEquals("'no1' 'no3' 'no2' 'no2' 'no3'", SQLUtils.formatSQL("@ @3 @ @2 @", "no1", "no2", "no3"));
		
		assertEquals("SELECT * FROM users", SQLUtils.formatSQL("SELECT * FROM users"));
		
		assertEquals("SELECT * FROM users WHERE ID='123' AND NAME='abc '''", 
				SQLUtils.formatSQL("SELECT * FROM @0 WHERE ID=@ AND NAME=@", "users", 123, "abc '"));
		
		assertEquals("INSERT INTO users (NAME, AGE, EMAIL) VALUES('abc ''',NULL,'')", 
				SQLUtils.formatSQL("INSERT INTO @01 (NAME, AGE, EMAIL) VALUES(@2,@3,@4)", "users", "abc '", null, ""));
		
		String sql = "UPDATE users SET password=@ WHERE id=@";
		Object[] params = { "\"; DROP TABLE users; show databases where \"\"=\"", 1 };
		String expected = "UPDATE users SET password='" + StringEscapeUtils.escapeSql(params[0].toString()) + "'" + 
				" WHERE id='1'";
		assertEquals(expected, SQLUtils.formatSQL(sql, params));
		params = new Object[] { "'; DROP TABLE users; show databases where ''='", 1 };
		expected = "UPDATE users SET password='" + StringEscapeUtils.escapeSql(params[0].toString()) + "'" + 
			" WHERE id='1'";
		assertEquals(expected, SQLUtils.formatSQL(sql, params));
		
		assertEquals("SELECT * FROM users WHERE EMAIL='a@b.c' AND NAME='abc '''", 
				SQLUtils.formatSQL("SELECT * FROM @0 WHERE EMAIL='a@@b.c' AND NAME=@", "users", "abc '"));
		assertEquals("SELECT * FROM users WHERE EMAIL='a@b.c' AND NAME='abc '''", 
				SQLUtils.formatSQL("SELECT * FROM @0 WHERE EMAIL=@ AND NAME=@", "users", "a@b.c", "abc '"));
		
		assertEquals("SELECT * FROM users WHERE ACTIVE='1' AND EMAIL='a@b.c' AND NAME='abc '''", 
				SQLUtils.formatSQL("SELECT * FROM @0 WHERE ACTIVE=@4 AND EMAIL=@ AND NAME=@", "users", "a@b.c", "abc '", 1));
		assertEquals("SELECT * FROM users WHERE ACTIVE=1 AND EMAIL='a@b.c' AND NAME='abc '''", 
				SQLUtils.formatSQL("SELECT * FROM @0 WHERE ACTIVE=@04 AND EMAIL=@ AND NAME=@", "users", "a@b.c", "abc '", 1));
		
		assertEquals("SELECT * FROM users WHERE FIELD='users' AND EMAIL='a@b.c' AND NAME!='a@b.c'", 
				SQLUtils.formatSQL("SELECT * FROM @01 WHERE FIELD=@ AND EMAIL=@ AND NAME!=@2", "users", "a@b.c"));
	}
	
}