Unable to insert data into MySQL

[ytfrank]

In white-box mode, EvoMaster seems unable to insert data into MySQL, thus failing to cover the code logic behind the if branch. Below log can be seen in the SUT:
2025-06-23 19:54:34 [qtp1652149987-14] INFO ks.long5.KSEmbeddedSutController - Tomcat started successfully on port 8080
2025-06-23 19:54:34 [qtp1652149987-14] INFO ks.long5.KSEmbeddedSutController - Configuring database connection...
WARN - Failed to handle regex in Matcher.find(): [&\s](?[\w.-\s%])(?:=(?[^&]*))?
2025-06-23 19:54:36 [qtp1652149987-14] INFO ks.long5.KSEmbeddedSutController - Database connection configured successfully
Not analyzing JPA using javax package
Failed to load Jakarta Persistence Layer classes
2025-06-23 19:56:56 [qtp1652149987-14] INFO ks.long5.KSEmbeddedSutController - Resetting state of SUT...

1. part of the source code
   CreditApply creditApply = creditApplyService.getByCreditNo(request.getNo());
   if (creditApply == null) {
   return CommonResp.fail(CommonCodeEnum.INVALID_PARAM, "DATA NOT EXIST");
   }

2. Is there any debug parameter when executing from the command line to get more information about DB operation? I tried running it for an hour, but it didn't seem to help.
   java -jar /Users/apple/gitrepo/EvoMaster/core/target/evomaster.jar --maxTime 300s --outputFolder "/Users/apple/gitrepo/EvoMaster/whitebox-deom/src/main/java/ks/long9"

---

| | | / | | |
| |_ _____ | . . | __ _ | | ___ _ __
| __\ \ / / _ | |/| |/ ` / | / _ \ '|
| |\ V / () | | | | (| __ \ || / |
_/ _/ _/_| |/_,|/____|_|

• EvoMaster version: 3.4.1-SNAPSHOT
• Loading configuration file from: /Users/apple/em.yaml
• Initializing...
• There is only 1 usable RESTful API endpoint defined in the schema configuration
• Starting to generate test cases
• Consumed search budget: 99.869%
• Covered targets: 323; time per test: 15.4ms (1.0 actions); since last improvement: 293s
• Starting to apply minimization phase
• Recomputing full coverage for 4 tests
• Analyzing 1 tests with size greater than 1
• Minimization progress: 1/1
• Minimization phase took 0 seconds
• Evaluated tests: 8753
• Evaluated actions: 9268
• Needed budget: 1%
• Passed time (seconds): 300
• Execution time per test (ms): Avg=28.40 , min=6.00 , max=23923.00
• Execution time per action (ms): Avg=26.54 , min=6.00 , max=23923.00
• Computation overhead between tests (ms): Avg=5.72 , min=1.00 , max=665.00
• Computation overhead of resetting the SUT (ms): Avg=6.12 , min=0.00 , max=23864.00
• Computation overhead of fetching test results, per test, subset of targets (ms): Avg=1.13 , min=0.00 , max=40.00
• Starting to apply security testing
• Going to save 3 tests to /Users/apple/gitrepo/EvoMaster/whitebox-deom/src/main/java/ks/long9
• Potential faults: 2
• Covered targets (lines, branches, faults, etc.): 357
• Bytecode line coverage: 5% (147 out of 2744 in 102 units/classes)
• Successfully executed (HTTP code 2xx) 1 endpoints out of 1 (100%)
• EvoMaster process has completed successfully

3. the driver class

   @OverRide
   public List getDbSpecifications() {
   return dbSpecification;
   }

   private void configureDatabaseConnection() {
   try {
   logger.info("Configuring database connection...");

        Class.forName("com.mysql.cj.jdbc.Driver");
        sqlConnection = DriverManager.getConnection(
                "jdbc:mysql://mysql.svc.cluster.local:3306/test?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false&nullCatalogMeansCurrent=true",
                "user",
                "pwd"
        );
   
        sqlConnection.setSchema("test");
   
        dbSpecification = Arrays.asList(new DbSpecification(DatabaseType.MYSQL, sqlConnection));
    } catch (ClassNotFoundException | SQLException e) {
        logger.error("Error while configuring database connection", e);
    }
   

   }
   @OverRide
   public String startSut() {
   try {
   logger.info("Starting SUT...");

        tomcat = new Tomcat();
        tomcat.setPort(8080);
        tomcat.getConnector();
   
   
        Context context = tomcat.addWebapp("/", "/Users/apple/gitrepo/test.war");
        tomcat.start();
        logger.info("Tomcat started successfully on port 8080");
   
        configureDatabaseConnection();
        logger.info("Database connection configured successfully");
   
        return "http://localhost:" + getSutPort();
    } catch (Exception e) {
        logger.error("Failed to start SUT", e);
        return "Failed to start Spring MVC application.";
    }
   

   }

Other important info:

version of EvoMaster (EM) used : latest code
how EM is run (eg, if from JAR or from one of its OS installers) : white-box
version of applicable runtimes (eg, JVM, NodeJS and .Net). For Java, can paste the output of java --version : 1.8.0
command-line options used to run EM

[arcuri82]

hi @ytfrank ,
thanks for reporting this potential issue. It could be a misconfiguration, or a bug in EM. bit hard to tell with this current information.
If you add the following:

--outputExecutedSQL ALL_AT_END

do you see anything generated in the sql.txt file? (same folder where you run EM)

[ytfrank]

Unfortunately, no file was found after adding the option. Is there any issue with dbSpecification in the driver class?
It is uncertain whether it was failed during database insertion or the test cases that require inserting into the database have not yet evolved.

[arcuri82]

@ytfrank can you paste the whole source code of the driver class?

[ytfrank]

Sure! Here is the source code:

package demo;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.example.demo.util.CryptoUtil;
import com.example.demo.vo.BindCardReq;
import com.example.demo.vo.CommonReq;
import org.apache.catalina.Context;
import org.apache.catalina.LifecycleException;
import org.apache.catalina.startup.Tomcat;
import org.apache.commons.lang3.RandomStringUtils;
import org.evomaster.client.java.controller.EmbeddedSutController;
import org.evomaster.client.java.controller.InstrumentedSutStarter;
import org.evomaster.client.java.controller.api.dto.SutInfoDto;
import org.evomaster.client.java.controller.api.dto.auth.AuthenticationDto;
import org.evomaster.client.java.controller.api.dto.database.schema.DatabaseType;
import org.evomaster.client.java.controller.internal.SutController;
import org.evomaster.client.java.controller.problem.ProblemInfo;
import org.evomaster.client.java.controller.problem.RestProblem;
import org.evomaster.client.java.controller.problem.param.DerivedParamContext;
import org.evomaster.client.java.controller.problem.param.RestDerivedParam;
import org.evomaster.client.java.sql.DbSpecification;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.servlet.DispatcherServlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

public class DDEmbeddedSutController extends EmbeddedSutController {

    private static final Logger logger = LoggerFactory.getLogger(KSEmbeddedSutController.class);

    private Tomcat tomcat;
    private DispatcherServlet dispatcherServlet;

    private Connection sqlConnection;
    private List<DbSpecification> dbSpecification;

    private final String OTHER_PARTY_PRIVATE_KEY = "MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDU/CNch/csgZeWd13yfGQF57HFEYRmpF90Prq7oXyr9MW0vky6bjh6W2OjQGoI6+iXAcGeU40zE3F1LdZ7Aao5zfWxwDb69a4/k+4QQL4S7TNJKzkCZXyob/cbXinXrCS1bBJWeJeBON3fpFjHjUrM7b1f2jvHW7VwEAoR5K1lBxIKrVOdEBYdMkOYl/LTuY8q9Ribn+SU/OsXLVam35nB5H2D2wCROJdk3dgc1DhvSPkQV1uzHtEVKFLhfCgrnuc7sESxnP/CvS/aAtaQI6OO5px8ge9Axo2AfepEY2cMUnNsI+sNQw6c+B+EQ0mYnDN/vbUjz5U352rSnDxXigGlAgMBAAECggEBAIw/N73PnniFIV+oXIm3VVn6FrpQ7aF1LZHrWKUDEohc1u9MvFOrDq+rjDHM0cXsKi19r5RlKj5u2DA0Ld6z2vbTY61XiBy7OLGO5J15NHBcF4Bf4NtuHqb/i3VPDTiCl22Lq4ubPxCNdLS2kOteY9oL1r7Ogryk7BXNygO8VF2IFdimtqUK8R+B9KT9zQZlZ0ePuwp8st2SD4w57b2weBWxrK651HnhWc3Jv+db7ioEi93nR6yNNHwkr05p2lz7QCazBMVS/8ku09V4cv7pFv8Qt1p4zmhIWM2SQwlsGqsVm396680pmaJcRhA/b9o4etgc7KXny+PkXWg4m2+CoWUCgYEA6eOdDfQZKN7MyJA4qRlNlXwkH1DkR2tyYpHp5wdLizd9OY0/VhJgWibXspQj2U1J6EOlmpWKxK/ppxAZQPigndWQ5WN0EFmsGw9f++xVmqb4y5dRoeEMX9MVUkpzYuj8D3xgpWLY/2G3Fa+CSAqIQwsYvrT3h/+g3Ymdd5VmB8sCgYEA6R6ebWULXicbQ1w2xYk35jhH6jSN5KF0AiUSTniY5I7jtPq6U+4UIC4ZVTcuzKna6oLYPo6Mz5/7xzAlFH3meXYMmzMidOlPLCaBRox0nud93+uQkzlUiAMP1Xnwj7aizGH0DNdvxHOaihwYrRJtV3aP0/Tjm8zBMkb45djRjk8CgYAI7PPAZZFUOvRrrv1KjrmkO8GRJGAhzZrm4hSgCWo7nNl9icNiAH+G0TaoDPk1QLrST0qRlKVgDiqHgpjEHvjaLR1Dc8EpIpLr0XFjRsiZh0e1VUf7OoBjY8XLhyXSH3wHB0MU7KnFTTBUdoctQqGilVjTZkqP+RRlf+vvdVcYcwKBgGRrayWGnGH/iI/4viNTm7/RSLFL2D+/iOCPpZYl/v6dytsgEnmrGgJOuVzq+37wQDw7BvnToDakHOpOA/sSzOBPiNimK1afVhqXr6J7SJVW9PMrX0j8Ljy0zPJRp93Qd8qA+4c4mo3OmqiZt8t+H5nImeZdHFr11ddZmUgxoPujAoGAONnom0TPxzr43oZG1I7bx9PqfYu+5yCce9czGo5zNuFj0ncV/ToiULLTSw7XhA3gbwoIe66tFxOlU8nYdlnhINWfL/o96u5G/k2RFAiOMH+h5IpimwjGoEkjbcvvNPjLKeoIUW6xtMjfXSmRPJhZQxy2DwCE8f9r61sqOlz26ig=";

    private final String YOUR_PUBLIC_KEY = "MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA0vlRB26swpZNF1ZCzIpGZXlPv/leNAjUdGGEhMDF5XD4MnchAIaaCv5YcWZ277Y5zWl0KcDIk0MwgxAJwxiKPOT1B5ngye4/ZnQ40P4mPnFC/s1YmS9OBAAfyv7iYUg1Hws6E7zBvXg+OaOOWCIvN8ZIcbzCwedXAr6KrlJ9OBEiB2PG0urXFnIWqnnk2qbD49JCIH2S0wk5Dr5/6VYKzvtYcvvk74VG4cGHjVcAGgBw44M2ags3xO7Z754aqVtae4TTcaTmc0UDDMLCjiplNep4EyMdgCC4N/ZeY8LrYrzszq+XJc7QB/dZS09GucaA5dIfgt14zlt2QfPRE4kI9QIDAQAB";

    private static final String aesKey = RandomStringUtils.randomAlphanumeric(16);
    public static void main(String[] args) {
        logger.info("****** main thread started ******");
        SutController controller = new KSEmbeddedSutController();
        InstrumentedSutStarter starter = new InstrumentedSutStarter(controller);

        starter.start();
        logger.info("****** main thread finished ******");
    }

    @Override
    public boolean isSutRunning() {
        boolean isRunning = tomcat != null && tomcat.getServer().getState().isAvailable();
        logger.info("SUT running state: {}", isRunning);
        return isRunning;
    }

    @Override
    public String getPackagePrefixesToCover() {
        return "com.app.test.dd";
    }

    @Override
    public List<AuthenticationDto> getInfoForAuthentication() {
        return null;
    }


    @Override
    public String deriveObjectParameterData(String paramName, String jsonObject, String endpointPath) throws Exception {

        if(paramName.equals("sign")){
            CommonReq req = JSONObject.parseObject(jsonObject, CommonReq.class);
            String signText = req.signText();
            return CryptoUtil.sign(signText, OTHER_PARTY_PRIVATE_KEY);
        }

        if(paramName.equals("key")){
            return CryptoUtil.encryptByPublicKey(aesKey, YOUR_PUBLIC_KEY);
        }



        if(paramName.equals("data")){
            CommonReq<BindCardReq> req = JSON.parseObject(jsonObject, new TypeReference<CommonReq<BindCardReq>>() {});
            return CryptoUtil.encrypt(JSONObject.toJSONString(req.getBizData()), aesKey);
        }

        throw new IllegalArgumentException("Unrecognized parameter: " + paramName);
    }



    @Override
    public ProblemInfo getProblemInfo() {
        return new RestProblem(
                "http://localhost:8000/v3/api-docs",
                null
        ).withDerivedParams(Arrays.asList(
                new RestDerivedParam("key", DerivedParamContext.BODY_PAYLOAD, null, 0),
                new RestDerivedParam("data", DerivedParamContext.BODY_PAYLOAD, null, 0),
                new RestDerivedParam("sign", DerivedParamContext.BODY_PAYLOAD, null, 1)
        ));
    }

    @Override
    public SutInfoDto.OutputFormat getPreferredOutputFormat() {
        return SutInfoDto.OutputFormat.JAVA_JUNIT_4;
    }

    @Override
    public String startSut() {
        try {
            logger.info("Starting SUT...");

            tomcat = new Tomcat();
            tomcat.setPort(8080);
            tomcat.getConnector();


            Context context = tomcat.addWebapp("/", "/Users/apple/test_war");
            tomcat.start();
            logger.info("Tomcat started successfully on port 8080");


            configureDatabaseConnection();
            logger.info("Database connection configured successfully");

            return "http://localhost:" + getSutPort();
        } catch (Exception e) {
            logger.error("Failed to start SUT", e);
            return "Failed to start Spring MVC application.";
        }
    }


    protected int getSutPort() {
        return 8080;
    }

    @Override
    public void stopSut() {
        if (tomcat != null) {
            try {
                tomcat.stop();
                logger.info("Tomcat stopped successfully");
            } catch (LifecycleException e) {
                logger.error("Error while stopping Tomcat", e);
            }
        }
    }

    @Override
    public void resetStateOfSUT() {
        logger.info("Resetting state of SUT...");
    }

    @Override
    public List<DbSpecification> getDbSpecifications() {
        return dbSpecification;
    }

    private void configureDatabaseConnection() {
        try {
            logger.info("Configuring database connection...");
            Class.forName("com.mysql.cj.jdbc.Driver");
            sqlConnection = DriverManager.getConnection(
                    "jdbc:mysql://mysql.local:3306/test?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&useSSL=false&nullCatalogMeansCurrent=true",
                    "user",
                    "pwd"
            );
            sqlConnection.setSchema("test");
            dbSpecification = Arrays.asList(new DbSpecification(DatabaseType.MYSQL, sqlConnection));
        } catch (ClassNotFoundException | SQLException e) {
            logger.error("Error while configuring database connection", e);
        }
    }
}

[arcuri82]

@ytfrank
hmmm... I do not see any specific issue in this driver. could it be that no data is added because no SQL command was ever executed in the API? I mean, you get a coverage of only 5% here.
we insert data into SQL databases only when there are SQL queries that are empty.

the 5% could be related to EM failing to generate valid inputs that satisfy first layer of input constraints. also, you have 102 classes, but only 1 single endpoint?

[arcuri82]

eg, in a previous example you made, there was:

    @Size(min = 16, max = 19)
    @Pattern(regexp = "^[0-9]+$")
    private String bankCardNo;

that type of double-constraint is not something we can handle now (and i ll not have time to fix this issue until after the summer, unfortunately).
but, that can be rewritten into:

    @Size(min = 16, max = 19)
    @Pattern(regexp = "^[0-9]{16,19}$")
    private String bankCardNo;

which we can handle

[ytfrank]

Currently only one api "/bind_card_apply" is testing.

@PostMapping("/bind_card_apply")
//public CommonResp bindCardApply(@RequestBody CommonReq req) throws Exception {
public ResponseEntity bindCardApply(@RequestBody CommonReq req) throws Exception {

     // 1. the verification of the request data
     // 2. The following line will query whether the specified data exists in the MySQL database table.
    //  If it does not exist, it will  return directly, so the code after that cannot be covered.
    CreditApply creditApply = creditApplyService.getByCreditNo(request.getNo());
    if (creditApply == null) {
    return CommonResp.fail(CommonCodeEnum.INVALID_PARAM, "DATA NOT EXIST");
    }
   --- CODE_NOT_COVERED ---

I will add the code about SQL operations into the following class, and then test it later.
e2e-tests/spring-rest-rsa/src/main/java/com/example/demo/controller/DemoController.java

[ytfrank]

Attached is the file "sql.txt" after adding the SQL operations to the API and running half an hour test.
All the lines like below:
"POST:/api/bind_card_apply","SELECT COUNT(1) FROM user WHERE id_card_no = '214210078932238752'",13,false
sql.txt

To facilitate testing and confirm the issue, I also committed a PR #1269

[ytfrank]

Hi, any plans about it?

[arcuri82]

Hi @ytfrank July is vacation period in Norway. I ll look at it once i m back

[ytfrank]

Have a good time on your vacation! Looking forward to seeing the issue fixed when you're back.

[arcuri82]

@ytfrank haven't forgotten about this issue. but we are in the middle of a major refactoring of SQL handling, which unfortunately it is taking much longer than expected. doubt will have time to look at this before October or November :(

[ytfrank]

Thanks for the update. Hope the SQL refactoring progresses smoothly. Let me know if you need any help later.