CVE-2014-125052: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in JervenBolleman sparql-identifiers and classified as critical. This issue affects some unknown processing of the file src/main/java/org/identifiers/db/RegistryDao.java. The manipulation leads to sql injection. The patch is named 44bb0db91c064e305b192fc73521d1dfd25bde52. It is recommended to apply a patch to fix this issue. The associated identifier of this vulnerability is VDB-217571.
CWE description: The product constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component. Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
CVSS v3 base score: 9.8
Published: 2023-01-06T21:15Z
Repository: https://github.com/JervenBolleman/sparql-identifiers
Language: Java
Files changed: src/main/java/org/identifiers/db/DbUtilities.java, src/main/java/org/identifiers/db/RegistryDao.java
Commit date: 2014-05-19 15:33:58 +0900
Commit message: Clean up DB code, remove some SQL injection holes.
Diff: diff --git a/src/main/java/org/identifiers/db/DbUtilities.java b/src/main/java/org/identifiers/db/DbUtilities.java
index c0c6aba..0c036ec 100644
--- a/src/main/java/org/identifiers/db/DbUtilities.java
+++ b/src/main/java/org/identifiers/db/DbUtilities.java
@@ -65,20 +65,15 @@ public class DbUtilities
 	/**
 	 * Closes the database connection, included the prepared statement.
 	 * @param connection
 	 * @param stmt
 	 */
-	public static void closeDbConnection(Connection connection, PreparedStatement stmt)
+	public static void closeDbConnection(Connection connection)
 	{
         // closes the connection
         try
         {
-            if (null !=  stmt)
-            {
-            	//System.out.println("- Closes the prepared statement");
-            	stmt.close();
-            }
             if (null != connection)
             {
             	//System.out.println("- Closes the connection");
             	connection.close();
             }
diff --git a/src/main/java/org/identifiers/db/RegistryDao.java b/src/main/java/org/identifiers/db/RegistryDao.java
index 3657a9b..e7cdb08 100644
--- a/src/main/java/org/identifiers/db/RegistryDao.java
+++ b/src/main/java/org/identifiers/db/RegistryDao.java
@@ -1,116 +1,92 @@
 package org.identifiers.db;
 
-
 import org.identifiers.data.URIextended;
 import org.identifiers.db.DbUtilities;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.List;
 
-
 /**
  * Simple dao for SPARQL testing.
  * 
  * @author Camille
  * @version 20140519
  */
-public class RegistryDao
-{
-	private Connection connection = null;
-	
-	
-	
-	
+public class RegistryDao {
+
 	/**
 	 * Returns all URIs sameAs the provided one.
+	 * 
 	 * @param uri
 	 * @return
 	 */
-	public List<URIextended> getSameAsURIs(String uri)
-	{
-        Boolean error = false;   // if an error happens
-        PreparedStatement stmt = null;
-        ResultSet rs;
-        List<URIextended> urls = null;
-        
-        // initialisation of the database connection
-	    connection = DbUtilities.initDbConnection();
-        
-        try
-        {
+	public List<URIextended> getSameAsURIs(String uri) {
+
+		List<URIextended> urls = null;
 
-            final String uriTobe = uri.substring(0,uri.indexOf("/", 10));
-			String query = "SELECT convertPrefix, ptr_datatype FROM mir_resource WHERE `convertPrefix` LIKE '"+uriTobe+"%'";
-            
-            try
-            {
-                stmt = connection.prepareStatement(query);
-            }
-            catch (SQLException e)
-            {
-                System.err.println("Error while creating the prepared statement!");
-                System.err.println("SQL Exception raised: " + e.getMessage());
-            }
-            
-            //logger.debug("SQL prepared query: " + stmt.toString());
-            rs = stmt.executeQuery();
+		// initialisation of the database connection
+		try (Connection connection = DbUtilities.initDbConnection()) {
 
-            String dataTypeId = null;
-            String identifier = null;
+			final String uriTobe = uri.substring(0, uri.indexOf("/", 10))+'%';
+			String query = "SELECT convertPrefix, ptr_datatype FROM mir_resource WHERE `convertPrefix` LIKE ?";
 
-            while (rs.next()) {
-                String convertPrefix = rs.getString("convertPrefix");
-                if(uri.contains(convertPrefix)){
-                    dataTypeId = rs.getString("ptr_datatype");
-                    identifier = uri.substring(convertPrefix.length());
-                }
+			String dataTypeId = null;
+			String identifier = null;
 
-            }
+			try (PreparedStatement stmt = connection.prepareStatement(query)) {
+				stmt.setString(1, uriTobe);
+				try (ResultSet rs = stmt.executeQuery()) {
+					while (rs.next()) {
+						String convertPrefix = rs.getString("convertPrefix");
+						if (uri.contains(convertPrefix)) {
+							dataTypeId = rs.getString("ptr_datatype");
+							identifier = uri.substring(convertPrefix.length());
+						}
+					}
+				}
+			} catch (SQLException e) {
+				System.err
+						.println("Error while creating the prepared statement!");
+				System.err.println("SQL Exception raised: " + e.getMessage());
+				throw new RuntimeException(
+						"Sorry, an error occurred while dealing with your request.",
+						e);
+			}
 
-            query = "SELECT convertPrefix, obsolete FROM mir_resource WHERE ptr_datatype=\""+dataTypeId+"\" and urischeme=1";
+			// logger.debug("SQL prepared query: " + stmt.toString());
 
-            try
-            {
-                stmt = connection.prepareStatement(query);
-            }
-            catch (SQLException e)
-            {
-                System.err.println("Error while creating the prepared statement!");
-                System.err.println("SQL Exception raised: " + e.getMessage());
-            }
-            //logger.debug("SQL prepared query: " + stmt.toString());
-            rs = stmt.executeQuery();
+			query = "SELECT convertPrefix, obsolete FROM mir_resource WHERE ptr_datatype=? and urischeme=1";
 
-            urls = new ArrayList<URIextended>();
-            while (rs.next())
-            {
-                urls.add(new URIextended(rs.getString("convertPrefix") + identifier, rs.getInt("obsolete")));
-            }
-            rs.close();
-        }
-        catch (SQLException e)
-        {
-            //logger.error("Error during the processing of the result of a query.");
-            //logger.error("SQL Exception raised: " + e.getMessage());
-            error = true;
-        }
-        finally
-        {
-        	// closes the database connection and statement
-            DbUtilities.closeDbConnection(connection, stmt);
-        }
+			try (PreparedStatement stmt = connection.prepareStatement(query)) {
+				stmt.setString(1, dataTypeId);
+				try (ResultSet rs = stmt.executeQuery()) {
 
+					urls = new ArrayList<URIextended>();
+					while (rs.next()) {
+						urls.add(new URIextended(rs.getString("convertPrefix")
+								+ identifier, rs.getInt("obsolete")));
+					}
+				}
+			} catch (SQLException e) {
+				System.err
+						.println("Error while creating the prepared statement!");
+				System.err.println("SQL Exception raised: " + e.getMessage());
+				throw new RuntimeException(
+						"Sorry, an error occurred while dealing with your request.",
+						e);
+			}
+			// logger.debug("SQL prepared query: " + stmt.toString());
 
-        // exception handling
-        if (error)
-        {
-            throw new RuntimeException("Sorry, an error occurred while dealing with your request.");
-        }
-        System.out.println("u"+urls.size());
-        return urls;
+		} catch (SQLException e1) {
+			throw new RuntimeException(
+					"Sorry, an error occurred while dealing with your request.",
+					e1);
+		}
+		System.out.println("u" + urls.size());
+		return urls;
 	}
 }

Vulnerable code: 	public static void closeDbConnection(Connection connection, PreparedStatement stmt)
            if (null !=  stmt)
            {
            	//System.out.println("- Closes the prepared statement");
            	stmt.close();
            }


public class RegistryDao
{
	private Connection connection = null;
	
	
	
	
	public List<URIextended> getSameAsURIs(String uri)
	{
        Boolean error = false;   // if an error happens
        PreparedStatement stmt = null;
        ResultSet rs;
        List<URIextended> urls = null;
        
        // initialisation of the database connection
	    connection = DbUtilities.initDbConnection();
        
        try
        {
            final String uriTobe = uri.substring(0,uri.indexOf("/", 10));
			String query = "SELECT convertPrefix, ptr_datatype FROM mir_resource WHERE `convertPrefix` LIKE '"+uriTobe+"%'";
            
            try
            {
                stmt = connection.prepareStatement(query);
            }
            catch (SQLException e)
            {
                System.err.println("Error while creating the prepared statement!");
                System.err.println("SQL Exception raised: " + e.getMessage());
            }
            
            //logger.debug("SQL prepared query: " + stmt.toString());
            rs = stmt.executeQuery();
            String dataTypeId = null;
            String identifier = null;
            while (rs.next()) {
                String convertPrefix = rs.getString("convertPrefix");
                if(uri.contains(convertPrefix)){
                    dataTypeId = rs.getString("ptr_datatype");
                    identifier = uri.substring(convertPrefix.length());
                }
            }
            query = "SELECT convertPrefix, obsolete FROM mir_resource WHERE ptr_datatype=\""+dataTypeId+"\" and urischeme=1";
            try
            {
                stmt = connection.prepareStatement(query);
            }
            catch (SQLException e)
            {
                System.err.println("Error while creating the prepared statement!");
                System.err.println("SQL Exception raised: " + e.getMessage());
            }
            //logger.debug("SQL prepared query: " + stmt.toString());
            rs = stmt.executeQuery();
            urls = new ArrayList<URIextended>();
            while (rs.next())
            {
                urls.add(new URIextended(rs.getString("convertPrefix") + identifier, rs.getInt("obsolete")));
            }
            rs.close();
        }
        catch (SQLException e)
        {
            //logger.error("Error during the processing of the result of a query.");
            //logger.error("SQL Exception raised: " + e.getMessage());
            error = true;
        }
        finally
        {
        	// closes the database connection and statement
            DbUtilities.closeDbConnection(connection, stmt);
        }
        // exception handling
        if (error)
        {
            throw new RuntimeException("Sorry, an error occurred while dealing with your request.");
        }
        System.out.println("u"+urls.size());
        return urls;
Fixed code: 	public static void closeDbConnection(Connection connection)
public class RegistryDao {

	 * 
	public List<URIextended> getSameAsURIs(String uri) {

		List<URIextended> urls = null;
		// initialisation of the database connection
		try (Connection connection = DbUtilities.initDbConnection()) {
			final String uriTobe = uri.substring(0, uri.indexOf("/", 10))+'%';
			String query = "SELECT convertPrefix, ptr_datatype FROM mir_resource WHERE `convertPrefix` LIKE ?";
			String dataTypeId = null;
			String identifier = null;
			try (PreparedStatement stmt = connection.prepareStatement(query)) {
				stmt.setString(1, uriTobe);
				try (ResultSet rs = stmt.executeQuery()) {
					while (rs.next()) {
						String convertPrefix = rs.getString("convertPrefix");
						if (uri.contains(convertPrefix)) {
							dataTypeId = rs.getString("ptr_datatype");
							identifier = uri.substring(convertPrefix.length());
						}
					}
				}
			} catch (SQLException e) {
				System.err
						.println("Error while creating the prepared statement!");
				System.err.println("SQL Exception raised: " + e.getMessage());
				throw new RuntimeException(
						"Sorry, an error occurred while dealing with your request.",
						e);
			}
			// logger.debug("SQL prepared query: " + stmt.toString());
			query = "SELECT convertPrefix, obsolete FROM mir_resource WHERE ptr_datatype=? and urischeme=1";
			try (PreparedStatement stmt = connection.prepareStatement(query)) {
				stmt.setString(1, dataTypeId);
				try (ResultSet rs = stmt.executeQuery()) {
					urls = new ArrayList<URIextended>();
					while (rs.next()) {
						urls.add(new URIextended(rs.getString("convertPrefix")
								+ identifier, rs.getInt("obsolete")));
					}
				}
			} catch (SQLException e) {
				System.err
						.println("Error while creating the prepared statement!");
				System.err.println("SQL Exception raised: " + e.getMessage());
				throw new RuntimeException(
						"Sorry, an error occurred while dealing with your request.",
						e);
			}
			// logger.debug("SQL prepared query: " + stmt.toString());
		} catch (SQLException e1) {
			throw new RuntimeException(
					"Sorry, an error occurred while dealing with your request.",
					e1);
		}
		System.out.println("u" + urls.size());
		return urls;