CVE-2016-15021: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') (CWE-89)
value: A vulnerability was found in nickzren alsdb. It has been rated as critical. This issue affects some unknown processing. The manipulation leads to sql injection. Upgrading to version v2 is able to address this issue. The identifier of the patch is cbc79a68145e845f951113d184b4de207c341599. It is recommended to upgrade the affected component. The identifier VDB-218429 was assigned to this vulnerability.
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-17T16:15Z
Repository: https://github.com/nickzren/alsdb
Language: Java
Files changed: src/main/java/model/Input.java, src/main/java/model/Output.java, src/main/java/object/Variant.java, src/main/java/util/DBManager.java
Commit date: 2016-10-19 15:29:48 -0400
Commit message: fixed sql injection vulnerability

Diff: diff --git a/src/main/java/model/Input.java b/src/main/java/model/Input.java
index 76aab5a..4998c00 100644
--- a/src/main/java/model/Input.java
+++ b/src/main/java/model/Input.java
@@ -1,7 +1,8 @@
 package model;
 
+import java.sql.PreparedStatement;
 import object.Region;
 import util.DBManager;
 import util.FormatManager;
 import java.sql.ResultSet;
 import java.util.ArrayList;
@@ -54,15 +55,15 @@ public class Input {
 
         return new Region(chr, start, end);
     }
 
     private static void initRegionListByGeneName(String geneName) throws Exception {
-        String sql = "SELECT * "
-                + "FROM gene_region "
-                + "WHERE gene_name='" + geneName + "'";
+        String sql = "SELECT * FROM gene_region WHERE gene_name=?";
 
-        ResultSet rset = DBManager.executeQuery(sql);
+        PreparedStatement stmt = DBManager.prepareStatement(sql);
+        stmt.setString(1, geneName);
+        ResultSet rset = stmt.executeQuery();
 
         if (rset.next()) {
             query = rset.getString("gene_name");
             String regionStr = rset.getString("region");
 
@@ -71,15 +72,15 @@ public class Input {
 
         rset.close();
     }
 
     private static void initRvisByGene(String geneName) throws Exception {
-        String sql = "SELECT * "
-                + "FROM rvis "
-                + "WHERE gene_name='" + geneName + "'";
+        String sql = "SELECT * FROM rvis WHERE gene_name=?";
 
-        ResultSet rset = DBManager.executeQuery(sql);
+        PreparedStatement stmt = DBManager.prepareStatement(sql);
+        stmt.setString(1, geneName);
+        ResultSet rset = stmt.executeQuery();
 
         if (rset.next()) {
             float f = FormatManager.getFloat(rset.getObject("rvis_percent"));
 
             String value = FormatManager.getString(f);
diff --git a/src/main/java/model/Output.java b/src/main/java/model/Output.java
index ad48bd2..f78fb34 100644
--- a/src/main/java/model/Output.java
+++ b/src/main/java/model/Output.java
@@ -1,7 +1,8 @@
 package model;
 
+import java.sql.PreparedStatement;
 import object.Region;
 import object.Variant;
 import util.DBManager;
 import java.sql.ResultSet;
 import java.util.ArrayList;
@@ -36,16 +37,18 @@ public class Output {
     public static void initVariant() throws Exception {
         String[] tmp = Input.idStr.split("-");
 
         String sql = "SELECT * "
                 + "FROM variant_v2 "
-                + "WHERE chr='" + tmp[0] + "' "
-                + "AND pos=" + tmp[1] + " "
-                + "AND ref='" + tmp[2] + "' "
-                + "AND allele='" + tmp[3] + "'";
-
-        ResultSet rset = DBManager.executeQuery(sql);
+                + "WHERE chr= ? AND pos= ? AND ref= ? AND allele= ?";
+
+        PreparedStatement stmt = DBManager.prepareStatement(sql);
+        stmt.setString(1, tmp[0]);
+        stmt.setInt(2, Integer.valueOf(tmp[1]));
+        stmt.setString(3, tmp[2]);
+        stmt.setString(4, tmp[3]);
+        ResultSet rset = stmt.executeQuery();
 
         if (rset.next()) {
             variant = new Variant(rset);
         }
 
diff --git a/src/main/java/object/Variant.java b/src/main/java/object/Variant.java
index 491f353..82aaf7f 100644
--- a/src/main/java/object/Variant.java
+++ b/src/main/java/object/Variant.java
@@ -1,7 +1,8 @@
 package object;
 
+import java.sql.PreparedStatement;
 import util.DBManager;
 import util.FormatManager;
 import java.sql.ResultSet;
 import java.util.ArrayList;
 import java.util.HashMap;
@@ -129,16 +130,18 @@ public class Variant {
     }
 
     public void initAnnotationMap() throws Exception {
         String sql = "SELECT * "
                 + "FROM annotation_v2 "
-                + "WHERE variant_id = " + id + " "
+                + "WHERE variant_id = ? "
                 + "ORDER BY igm_rank,"
                 // when igm_rank is the same, the data sort by "Canonical" = "YES"
                 + "case when canonical is null then 1 else 0 end,canonical;";
 
-        ResultSet rset = DBManager.executeQuery(sql);
+        PreparedStatement stmt = DBManager.prepareStatement(sql);
+        stmt.setInt(1, id);
+        ResultSet rset = stmt.executeQuery();
 
         while (rset.next()) {
             Annotation anno = new Annotation(rset);
 
             if (annotation == null) {
diff --git a/src/main/java/util/DBManager.java b/src/main/java/util/DBManager.java
index 661bd2c..6e5e1a7 100644
--- a/src/main/java/util/DBManager.java
+++ b/src/main/java/util/DBManager.java
@@ -84,6 +84,10 @@ public class DBManager {
     }
 
     public static ResultSet executeQuery(String sqlQuery) throws SQLException {
         return statement.executeQuery(sqlQuery);
     }
+    
+    public static PreparedStatement prepareStatement(String sqlQuery) throws SQLException{
+        return connection.prepareStatement(sqlQuery);
+    }
 }

Vulnerable code:         String sql = "SELECT * "
                + "FROM gene_region "
                + "WHERE gene_name='" + geneName + "'";
        ResultSet rset = DBManager.executeQuery(sql);
        String sql = "SELECT * "
                + "FROM rvis "
                + "WHERE gene_name='" + geneName + "'";
        ResultSet rset = DBManager.executeQuery(sql);
                + "WHERE chr='" + tmp[0] + "' "
                + "AND pos=" + tmp[1] + " "
                + "AND ref='" + tmp[2] + "' "
                + "AND allele='" + tmp[3] + "'";

        ResultSet rset = DBManager.executeQuery(sql);
                + "WHERE variant_id = " + id + " "
        ResultSet rset = DBManager.executeQuery(sql);
Fixed code: import java.sql.PreparedStatement;
        String sql = "SELECT * FROM gene_region WHERE gene_name=?";
        PreparedStatement stmt = DBManager.prepareStatement(sql);
        stmt.setString(1, geneName);
        ResultSet rset = stmt.executeQuery();
        String sql = "SELECT * FROM rvis WHERE gene_name=?";
        PreparedStatement stmt = DBManager.prepareStatement(sql);
        stmt.setString(1, geneName);
        ResultSet rset = stmt.executeQuery();
import java.sql.PreparedStatement;
                + "WHERE chr= ? AND pos= ? AND ref= ? AND allele= ?";

        PreparedStatement stmt = DBManager.prepareStatement(sql);
        stmt.setString(1, tmp[0]);
        stmt.setInt(2, Integer.valueOf(tmp[1]));
        stmt.setString(3, tmp[2]);
        stmt.setString(4, tmp[3]);
        ResultSet rset = stmt.executeQuery();
import java.sql.PreparedStatement;
                + "WHERE variant_id = ? "
        PreparedStatement stmt = DBManager.prepareStatement(sql);
        stmt.setInt(1, id);
        ResultSet rset = stmt.executeQuery();
    
    public static PreparedStatement prepareStatement(String sqlQuery) throws SQLException{
        return connection.prepareStatement(sqlQuery);
    }