How can I prevent SQL injection in my code? [duplicate]

Question

I have a query where there is sql injection in line .I'm currently working on below code

I noticed that my query is vulnerable to SQL injection. Here's the part of the code that’s causing concern:

return iSearchResultsService.getSearchResults(searchresultbean);

How can I rewrite this query to prevent SQL injection?

I’ve heard about parameterized queries or prepared statements, but I’m not sure how to apply that in my case.


 public List<SearchResultPojo> getSearchResults(
            @RequestBody SearchResultsBean searchresultbean,
            @PathVariable("cId") int catalogId, @PathVariable("region") String region,
            @PathVariable("languageId") int languageId) {
        logger.info("  Search Result Controller Started");

        if(catalogId >= 0) {
            int catId=Integer.parseInt(Integer.toString(catalogId));
            searchresultbean.setCatId(catId);
        } else {
            searchresultbean.setCatId(catalogId);
        }

        searchresultbean.setLangId(languageId);
        searchresultbean.setRegion(region);

        char[][] replaceChars = new char[1][2];
        char[] badChars = new char[2];

        // Characters to be replaced (e.g. replace '*' with '%')
        replaceChars[0][0] = '*';
        replaceChars[0][1] = '%';

        // Characters to be removed from product description search
        badChars[0] = '*';
        badChars[1] = '\'';

        searchresultbean.setReplaceChars(replaceChars);
        searchresultbean.setBadChars(badChars);

        Map<String, Object> buuidMap = iSearchResultsService.getUnitId(region, cId,
                languageId);
        if (buuidMap.size() > 0) {
            searchresultbean.setBuId((int) buuidMap.get("business_unit_id"));
        }
        logger.info(" Product Search Result Controller Exit");
        return iSearchResultsService.SearchResult(searchresultbean);

    }

}



    public String getSearchQuery(searchresultbean searchresultbean) {
        logger.info("Search Result Sql QueryBuild function started");
        searchresultbean.setReturnStyle(1);
        StringBuilder queryString = new StringBuilder();
        queryString.append(buildSQLSelectFields(searchresultbean));
        queryString.append(buildSQLFrom(searchresultbean));
        queryString.append(buildSQLWhereClause(searchresultbean));
        queryString.append(this.buildSQLOrderBy(searchresultbean.getOrderBy()));
        logger.info(" Search Result Sql QueryBuild function Exit");
        return queryString.toString();
    }



}


private String buildSQLWhereClause(searchresultbean searchresultbean) {
        logger.info("Inside the Search Result Sql WHERE CLAUSE function started");
        StringBuilder whereClause = new StringBuilder(" WHERE 1 = 1 ");

        if (searchresultbean.getDId() > 0) {
            whereClause.append(
                    SqlHelper.getSqlArgs("AND", "v.id", String.valueOf(searchresultbean.getDId()),
                            SqlHelper.NUMBER, false, true, searchresultbean.getReplaceChars()));
        }

        if (!StringUtils.isEmpty(searchresultbean.getNum())) {
            whereClause
                    .append(SqlHelper.getSqlArgs("AND", "v._num", searchresultbean.getNum(),
                            SqlHelper.TEXT, true, true, searchresultbean.getReplaceChars()));
        }

        if (!StringUtils.isEmpty(searchresultbean.getDesc())) {
            boolean flag = searchresultbean.isUseBoolAndWhereClause();
            whereClause.append(buildDescClause(searchresultbean.getDesc(),
                    searchresultbean.getBadChars(), flag));
        }

        if (searchresultbean.getId() > 0) {
            whereClause.append(
                    SqlHelper.getSqlArgs("AND", "v.id", String.valueOf(searchresultbean.getId()),
                            SqlHelper.NUMBER, false, true, searchresultbean.getReplaceChars()));
        }

        if (!StringUtils.isEmpty(searchresultbean.getName())) {
            whereClause.append(
                    SqlHelper.getSqlArgs("AND", "v.name", searchresultbean.getName(),
                            SqlHelper.TEXT, true, true, searchresultbean.getReplaceChars()));
        }

        if (!StringUtils.isEmpty(searchresultbean.getNum())) {
            whereClause
                    .append(SqlHelper.getSqlArgs("AND", "v.num", searchresultbean.getNum(),
                            SqlHelper.TEXT, true, true, searchresultbean.getReplaceChars()));
        }

        if (!StringUtils.isEmpty(searchresultbean.getStdNum())) {
            whereClause.append(
                    SqlHelper.getSqlArgs("AND", "v.stdnum", searchresultbean.getStdNum(),
                            SqlHelper.TEXT, true, true, searchresultbean.getReplaceChars()));
        }

        if (!searchresultbean.getIncludeOff()) {
            whereClause.append(" AND v.flag = 1");
        }

        if (searchresultbean.getFilterFlag()) {
            whereClause.append(" AND v.flag1 = 1");
        }
        whereClause.append(" AND v.ID=").append(searchresultbean.getId()).append(' ');

        whereClause.append("AND ISNULL(bup.FLAG, 0) != 1 ");

        // Software Type filter
        if (searchresultbean.getSOFTWAREId() > 0) {
            whereClause.append(" AND dp.software_type_id = ");
            whereClause.append(searchresultbean.getSoftwareId() + "\n");
        }

        // Licensing filters
        if (searchresultbean.isLSearch()) {

            buildWhereClauseForsodtware(whereClause, searchresultbean);
        }

        logger.info("Search Result Sql WHERE CLAUSE");
        return whereClause.toString();
    }

How can we avoid these vulnerabilities ?

Prepared Statements en.wikipedia.org/wiki/Prepared_statement — aran
– aran, Commented May 5 at 8:48
I could provide you with an answer here, but I think a better idea would be for you to read about prepared statements; and try to understand what they look like, why we need them, and how you'd make one in your code above. You'll have to make two passes through your list of fields - one to make the prepared statement, then one to set the parameters. Please comment here once you've read up on prepared statements, if it's still not clear what to do, and I'll try to either explain more fully, or find you a similar example online. — Dawood ibn Kareem
– Dawood ibn Kareem, Commented May 5 at 8:50
The question I linked above is focussed on PHP, but the essence is the same because this is really an SQL problem. Read and digest. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented May 5 at 9:48
@DawoodibnKareem i have read it ready and and which method i need to finetune ,buildSQLWhereClause? — femalesoftwareengineer
– femalesoftwareengineer, Commented May 6 at 2:10
Unfortunately, the two questions that have been flagged as duplicates of this one are different enough to be unhelpful. I now can't answer this without reopening it; and I'm not sure whether I'm able to do that. You'll definitely have to tweak buildSQLWhereClause, so that whenever any of the fields in searchresultbean is populated, you'll add something to the SQL such as AND v.id = ? or AND v._num = ? and so on - use a ? instead of the actual value. But you'll also need to write a second method, which iterates through the fields again and sets the parameters in the prepared statement — Dawood ibn Kareem
– Dawood ibn Kareem, Commented May 6 at 8:23

Yash Mote · Accepted Answer · 2025-05-05 11:48:13Z

0

Wherever the SQL query is being built, you can do something like this:

String sql = "SELECT * FROM products WHERE id = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, searchresultbean.getId());
ResultSet rs = stmt.executeQuery();

This way, you avoid SQL injection completely since you're not manually concatenating user input into the SQL string.

edited May 5 at 11:48

answered May 5 at 9:43

Yash Mote

613 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abra May 5 at 10:48

Unless I missed something, the code for method getProductSearchResult was not posted, so how do you know what to change in it?

Yash Mote May 5 at 11:47

I actually meant wherever the SQL query is being built in the code.

femalesoftwareengineer May 6 at 2:11

@abra its getSearchResults

Abra May 6 at 3:31

@femalesoftwareengineer when I wrote the comment it was getProductSearchResult. Pardon me for not updating my comment after you edited your question and changed the method name.

Collectives™ on Stack Overflow

How can I prevent SQL injection in my code? [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related