How to prevent sql injection when using spring data couchbase with Query annotation?

[gotroyilmi]

How to prevent sql injection when using spring data couchbase with Query annotation?

I am having Controller, Service, Repository kind of Spring web project having enterprise couchbase server as DB, I am using spring-data-couchbase for querying data of couchbase, and I am using @Query Annotation to put custom queries.

Recently I came across a case where, i am having @Param String name as method param. in one of the request, i got name param’s value was having escaped double quotes, which has broken my couchbase query which is complex query but its simple representation is below

@Query(
“”"
#{n1ql.selectEntity} WHERE #{n1ql.filter}
AND name = “#{#name}” AND …
“”")

→ I want to sanitize value having escaped double quotes in “#{#name}”, using standard owasp library, and I do not want to do custom regex things.

Ref. How to prevent sql injection when using spring data couchbase with Query annotation? - Stack Overflow

[mreiche]

Use $name (no quotes) instead of “#{#name}”. Using #{#name} is handled by the Spring SPEL parser and is simply a string replace. $name (or $1) is a n1ql query parameter and there is no possibility for sql injection.

[gotroyilmi]

@Query(
      """
              #{#n1ql.selectEntity} WHERE #{#n1ql.filter}
              AND name = $name
          """)
  List<Info> getInfoByName(@Param("name") String name);

Thanks m-reiche for answer,
above is loose description of my InfoRepository.java file’s method to extract data from couchbase, can I still use @Param annotation of spring-data-couchbase, in case i am doine $name instead of “#{#name}”

[mreiche]

It’s not necessary to define the Query string for that query as Spring Data Couchbase will generate it based on the method signature.

The document is here
https://docs.spring.io/spring-data/couchbase/reference/couchbase.html

There are hundreds of examples in spring-data-couchbase/src/test/java/org/springframework/data/couchbase/domain at main · spring-projects/spring-data-couchbase · GitHub

[gotroyilmi]

@mreiche Thanks for ref

[househippo]

You can create Prepared Statements in Couchbase and pass in $varibles into the query too.

PREPARE customer_v1 AS SELECT orderId,date,status FROM bucket
WHERE customerId = $cust and date = $date ORDER BY date DESC
LIMIT $limit OFFSET $offset

If you create an index like this

CREATE INDEX customerOrder_v1 ON bucket(customerId,date DESC, status,orderId);

run/execute the prepared statement like below

EXECUTE customer_v1 USING {“cust”:“123”,“date”:“today”,“limit”:10,“offset”:0}

To track the usage and general performance of the prepared statement run this query

SELECT * FROM system:prepared WHERE name = “customer_v1”

Docs on prepared statements: Prepared Statements | Couchbase Docs