Checkmarx Postgres查询构建错误SQL注入错误,SQL二次注入错误Java Springboot

huangapple go评论62阅读模式
英文:

Checkmarx Postgres Query forming error SQL Injection error , SQL Second order injection error Java Springboot

问题

以下为翻译内容:

错误信息

应用程序的CreateDatabaseAndMapToDomain方法在daas-springboot-CheckMarxIntegration\src\main\java\com\it\daas\apis\service\PostgresConnectionServiceImpl.java的第717行执行一个带有executeQuery的SQL查询。应用程序通过将不受信任的字符串嵌入查询而构造此SQL查询,但没有进行适当的净化处理。连接的字符串被提交到数据库,然后在数据库中解析和执行。

攻击者可能能够将任意数据写入数据库,然后应用程序在daas-springboot-CheckMarxIntegration\src\main\java\com\it\daas\apis\service\PostgresConnectionServiceImpl.java的第678行的getDomains方法中使用executeQuery检索数据。然后,该数据在代码中传递,直到在没有进行净化处理的情况下直接在SQL查询中使用,并提交到数据库服务器以进行执行。

函数代码

@Override
public String getDomains() throws SQLException {

    String domainquery = "SELECT id,domain FROM domain";
    Connection con = null;
    PreparedStatement st = null;
    ResultSet result = null;
    try
    {
        con = ConnectToPostgresapibuilderDatabase(apibuilderConnectionString, apibuilderUserName, apibuilderPassword);
        con.setSchema(this.schema);
        st = con.prepareStatement(domainquery);
        result = st.executeQuery();
     
        ArrayList<DatabaseDomainBean> list = new ArrayList<DatabaseDomainBean>();
        while (result.next()) {
            list.add(new DatabaseDomainBean(result.getString("domain"), result.getString("id")));
        }
        return new JSONObject().put("domainlist", list).toString();


    }
    catch(Exception e)
    {

        e.printStackTrace();
        return null;
    }
    finally{

        result.close();
        st.close();
        con.close();

    }
}

@Override
public String CreateDatabaseAndMapToDomain(String database, String[] domainIds, String password)
        throws SQLException {

    if (!this.apibuilderPassword.equals(password)) {
        return null;
    }
    else if(database==null || domainIds==null || password ==null)
    {
        return "Failure";
    }

    Connection con = null;
    PreparedStatement st = null;
    ResultSet ispresent = null;
    PreparedStatement mapquerystmnt = null;
    ResultSet resultMapping = null;

    try
    {
        con = ConnectToPostgresapibuilderDatabase(apibuilderConnectionString, apibuilderUserName, apibuilderPassword);
        con.setSchema(this.schema);

        for (int i = 0; i < domainIds.length; i++) {

            con.setSchema(this.schema);

            String IfExists = MessageFormat.format(
                    "SELECT databasename,domainid FROM Databases WHERE databasename IN (?) AND domainid IN (?)",
                    Utilitymethods.ConvertToMessageFormatCompatibleForm(database),
                    Utilitymethods.ConvertToMessageFormatCompatibleForm(domainIds[i]));

            st = con.prepareStatement(IfExists);
            st.setString(1, database);
            st.setInt(2, Integer.parseInt(domainIds[i]));
            ispresent = st.executeQuery();

            if (!ispresent.next()) {
                // INSERT INTO Databases (databasename,domainid) VALUES ('Teradata','1')
                String mapquery = MessageFormat.format(
                        "INSERT INTO Databases (databasename,domainid) VALUES (?,?) returning Id",
                        Utilitymethods.ConvertToMessageFormatCompatibleForm(database),
                        Utilitymethods.ConvertToMessageFormatCompatibleForm(domainIds[i]));
                 mapquerystmnt = con.prepareStatement(mapquery);
                 mapquerystmnt.setString(1, database);
                 mapquerystmnt.setInt(2, Integer.parseInt(domainIds[i]));
                 resultMapping =  mapquerystmnt.executeQuery();
            }
        }

        return "Success";
    }
    catch(Exception e)
    {
        e.printStackTrace();
        return "Failure";
    }
    finally
    {
        ispresent.close();
        st.close();
        resultMapping.close();
        mapquerystmnt.close();
        con.close();
    }
}

以上是你提供的内容的翻译。如有需要,请随时告知。

英文:

I've some APIs that perform Database operations . Whenever I upload my code to checkmarx I get the following error and it is flagged as a high vulnerability error Can anyone help me ?

Error

The application's CreateDatabaseAndMapToDomain method executes an SQL query with executeQuery, at line 717 of daas-springboot-CheckMarxIntegration\src\main\java\com\it\daas\apis\service\PostgresConnectionServiceImpl.java. The application constructs this SQL query by embedding an untrusted string into the query without proper sanitization. The concatenated string is submitted to the database, where it is parsed and executed accordingly.

The attacker may be able to write arbitrary data to the database, which is then retrieved by the application with executeQuery in getDomains method at line 678 of daas-springboot-CheckMarxIntegration\src\main\java\com\it\daas\apis\service\PostgresConnectionServiceImpl.java. This data then flows through the code, until it is used directly in the SQL query without sanitization, and then submitted to the database server for execution

Here're my functions

    @Override
public String getDomains() throws SQLException {
String domainquery = &quot;SELECT id,domain FROM domain&quot;;
Connection con = null;
PreparedStatement st = null;
ResultSet result = null;
try
{
con = ConnectToPostgresapibuilderDatabase(apibuilderConnectionString, apibuilderUserName,apibuilderPassword);
con.setSchema(this.schema);
st = con.prepareStatement(domainquery);
result = st.executeQuery();
ArrayList&lt;DatabaseDomainBean&gt; list = new ArrayList&lt;DatabaseDomainBean&gt;();
while (result.next()) {
list.add(new DatabaseDomainBean(result.getString(&quot;domain&quot;), result.getString(&quot;id&quot;)));
}
return new JSONObject().put(&quot;domainlist&quot;, list).toString();
}
catch(Exception e)
{
e.printStackTrace();
return null;
}
finally{
result.close();
st.close();
con.close();
}
}
@Override
public String CreateDatabaseAndMapToDomain(String database, String[] domainIds, String password)
throws SQLException {
if (!this.apibuilderPassword.equals(password)) {
return null;
}
else if(database==null || domainIds==null || password ==null)
{
return &quot;Failure&quot;;
}
Connection con =null;
PreparedStatement st =null;
ResultSet ispresent =null;
PreparedStatement mapquerystmnt =null;
ResultSet resultMapping = null;
try
{
con = ConnectToPostgresapibuilderDatabase(apibuilderConnectionString, apibuilderUserName,apibuilderPassword);
con.setSchema(this.schema);
for (int i = 0; i &lt; domainIds.length; i++) {
con.setSchema(this.schema);
String IfExists = MessageFormat.format(
&quot;SELECT databasename,domainid FROM Databases WHERE databasename IN (?) AND domainid IN (?)&quot;,
Utilitymethods.ConvertToMessageFormatCompatibleForm(database),
Utilitymethods.ConvertToMessageFormatCompatibleForm(domainIds[i]));
st = con.prepareStatement(IfExists);
st.setString(1, database);
st.setInt(2, Integer.parseInt(domainIds[i]));
ispresent = st.executeQuery();
if (!ispresent.next()) {
// INSERT INTO Databases (databasename,domainid) VALUES (&#39;Teradata&#39;,&#39;1&#39;)
String mapquery = MessageFormat.format(
&quot;INSERT INTO Databases (databasename,domainid) VALUES (?,?) returning Id&quot;,
Utilitymethods.ConvertToMessageFormatCompatibleForm(database),
Utilitymethods.ConvertToMessageFormatCompatibleForm(domainIds[i]));
mapquerystmnt = con.prepareStatement(mapquery);
mapquerystmnt.setString(1, database);
mapquerystmnt.setInt(2, Integer.parseInt(domainIds[i]));
resultMapping =  mapquerystmnt.executeQuery();
}
}
return &quot;Success&quot;;
}
catch(Exception e)
{
e.printStackTrace();
return &quot;Failure&quot;;
}
finally
{
ispresent.close();
st.close();
resultMapping.close();
mapquerystmnt.close();
con.close();
}
}
@Override
public String getDomains() throws SQLException {
String domainquery = &quot;SELECT id,domain FROM domain&quot;;
Connection con = null;
PreparedStatement st = null;
ResultSet result = null;
try
{
con = ConnectToPostgresapibuilderDatabase(apibuilderConnectionString, apibuilderUserName,apibuilderPassword);
con.setSchema(this.schema);
st = con.prepareStatement(domainquery);
result = st.executeQuery();
ArrayList&lt;DatabaseDomainBean&gt; list = new ArrayList&lt;DatabaseDomainBean&gt;();
while (result.next()) {
list.add(new DatabaseDomainBean(result.getString(&quot;domain&quot;), result.getString(&quot;id&quot;)));
}
return new JSONObject().put(&quot;domainlist&quot;, list).toString();
}
catch(Exception e)
{
e.printStackTrace();
return null;
}
finally{
result.close();
st.close();
con.close();
}
}

答案1

得分: 1

尝试直接使用 st = con.prepareStatement("SELECT id,domain FROM domain")。看起来 Checkmarx 由于 String domainquery = "SELECT id,domain FROM domain" 语句以及可能的 SQL 注入可能性而产生了混淆。

英文:

Try with st = con.prepareStatement(&quot;SELECT id,domain FROM domain&quot;) directly. Looks like checkmarx confused due to String domainquery = &quot;SELECT id,domain FROM domain&quot; statement and the possibility of sql injection.

huangapple
  • 本文由 发表于 2020年5月29日 16:43:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/62081979.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定