Here is an example to inject dirty SQL using JDBC Statement:
package com.liguoliang.j2ee;
import static org.junit.Assert.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import org.junit.Before;
import org.junit.Test;
public class TestJDBC {
private static final String DB_URL = "jdbc:derby:C:\\Users\\Guoliang\\MyDB;create=true";
Connection conn = null ;
@Before
public void before() throws ClassNotFoundException, SQLException {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver") ;
conn = DriverManager.getConnection(DB_URL);
}
@Test
public void testSQLInjection() throws SQLException {
String userName = "guoliang' OR 'a' = 'a";
String password = "wrong-password" + new Date().toString();
Statement statement = conn.createStatement();
String sql = "SELECT * FROM USERS WHERE USER_NAME = '" + userName + "' AND PASSWORD = '" + password + "'";
System.out.println(sql);
ResultSet rs = statement.executeQuery(sql);
int userId = -1;
while (rs.next()) {
userId= rs.getInt(1);
System.out.println(" > User Id: " + userId); // Will print all user id;
}
assertTrue(userId != -1);
}
@Test
public void testPreparedStatement() throws SQLException {
String userName = "guoliang' OR 'a' = 'a";
String password = "wrong-password";
PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERS WHERE USER_NAME = ? AND PASSWORD = ?");
ps.setString(1, userName);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
int userId = -1;
while (rs.next()) {
userId= rs.getInt(1);
fail("User Id: " + userId);
}
System.out.println("userId: " + userId);
}
}
I set one of the paramters to : [guoliang ‘a’ = ‘a], the I got the SQL:
SELECT * FROM USERS WHERE USER_NAME = 'guoliang' OR 'a' = 'a' AND PASSWORD = 'wrong-passwordWed May 07 23:47:11 CST 2014'
This means, the SQL will return all records in this table.
However we can use prepared statement to solve this issue.
SQLs used by this example:
CREATE TABLE USERS (ID INT, USER_NAME VARCHAR(20), PASSWORD VARCHAR(20)); INSERT INTO USERS (ID, USER_NAME, PASSWORD) VALUES (1, 'guoliang', 'password_abc');
Solution A: Modify /usr/bin/java link
# which java /usr/bin/java # java -version java version "1.7.0_25" OpenJDK Runtime Environment (IcedTea 2.3.12) (7u25-2.3.12-4ubuntu3) OpenJDK 64-Bit Server VM (build 23.7-b01, mixed mode) # ls -ld /usr/bin/java lrwxrwxrwx 1 root root 22 Apr 16 21:47 /usr/bin/java -> /etc/alternatives/java # rm /usr/bin/java # ln -s /guoliangDev/tools/sun-jdk/jdk1.7.0_55/bin/java /usr/bin/java # java -version java version "1.7.0_55" Java(TM) SE Runtime Environment (build 1.7.0_55-b13) Java HotSpot(TM) 64-Bit Server VM (build 24.55-b03, mixed mode)
Solution B: Modify /etc/profile
#vi /etc/profileAppend:
export JAVA_HOME=/guoliangDev/tools/sun-jdk/jdk1.7.0_55 export PATH=$JAVA_HOME/bin:$PATH
save the file, and
source /etc/proflieRequirement: HttpsURLConnection ignore SSL Certificate Error / Accept all Certificate
package com.test;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSession;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
public final class SSLUtils {
public static void trustCertificate () throws KeyManagementException, NoSuchAlgorithmException {
System.setProperty ( "https.protocols", "SSLv3" );
TrustManager[] trustAllCerts = new TrustManager[] {
new X509TrustManager() {
@Override
public void checkClientTrusted(X509Certificate[] arg0,
String arg1) throws CertificateException {
System.out.println ( "checkClientTrusted()" );
}
@Override
public void checkServerTrusted(X509Certificate[] arg0,
String arg1) throws CertificateException {
System.out.println ( "checkServerTrusted()" );
}
@Override
public X509Certificate[] getAcceptedIssuers() {
System.out.println ( "X509Certificate()" );
return new X509Certificate[0];
}
}
};
final SSLContext sc = SSLContext.getInstance("SSLv3");
sc.init(null, trustAllCerts, new java.security.SecureRandom());
HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
HttpsURLConnection.setDefaultHostnameVerifier ( new HostnameVerifier () {
@Override
public boolean verify(String arg0, SSLSession arg1) {
return true;
}
} );
}
}
Usage:
before url.openConnection() call trustCertificate ();
Requirement: disable auto redirection in HttpClient. e.g. for test purpose.
// HttpClient Version: 4.3.3
RequestConfig requestConfig = RequestConfig.custom().setRedirectsEnabled(false).build();
httpGet.setConfig(requestConfig);
CloseableHttpResponse response = httpClient.execute(httpGet);
System.out.println("Status Code: " + response.getStatusLine().getStatusCode());
// Older version: http://stackoverflow.com/questions/1519392/how-to-prevent-apache-http-client-from-following-a-redirect
// HTTP parameters stores header etc.
HttpParams params = new BasicHttpParams();
params.setParameter("http.protocol.handle-redirects",false);
httpget.setParams(params);
1. Set global resource in server.xml
<GlobalNamingResources>
<Resource name="jdbc/DatabaseName" auth="Container" type="javax.sql.DataSource"
username="dbUsername" password="dbPasswd"
url="jdbc:postgresql://localhost/dbname"
driverClassName="org.postgresql.Driver"
initialSize="5" maxWait="5000"
maxActive="120" maxIdle="5"
validationQuery="select 1"
poolPreparedStatements="true"/>
</GlobalNamingResources/>
2. use it in the spring config file:
<bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="java:comp/env/jdbc/DatabaseName"/>
</bean>Ref: http://stackoverflow.com/questions/9183321/how-to-use-jndi-datasource-provided-by-tomcat-in-spring/9183486#9183486// Proudly powered by Apache, PHP, MySQL, WordPress, Bootstrap, etc,.