Skip to content

Potential SQL injection in example code #7

Open
@MKusber

Description

@MKusber

Referring to code samples like here:

const posts = await cds.run(
`WITH GEO_COORD AS (
SELECT ID, TEXT, DATE, ADDRESS, DECISION, PRIORITYDESC, GENAISUMMARY, GENAIDESCRIPTION,
MAINTENANCENOTIFICATIONID, CATEGORY, SENTIMENT, LOCATION, LONG, LAT, NEW ST_Point(LONG, LAT, 4326) LONG_LAT_GEO,
CASE WHEN
LAT IS NOT NULL AND LONG IS NOT NULL THEN
New ST_Point(${long}, ${lat}, 4326).ST_Distance(NEW ST_Point(LONG, LAT, 4326),'meter')
ELSE NULL
END as "DISTANCE_METER", L2DISTANCE(VECTOR, TO_REAL_VECTOR('[${embeddings[0].toString()}]')) as "similarity"
FROM "SOCIAL_CITIZEN_GENAI_PROCESSEDISSUES"
WHERE
(DATE <= TO_DATE('${date}', 'YYYY-MM-DD') AND DATE >= ADD_DAYS(TO_DATE('${date}', 'YYYY-MM-DD'), -${days})) AND
L2DISTANCE(VECTOR, TO_REAL_VECTOR('[${embeddings[0].toString()}]')) < 0.6
)
SELECT ID, TEXT, DATE, ADDRESS, DECISION, PRIORITYDESC, GENAISUMMARY, GENAIDESCRIPTION,
MAINTENANCENOTIFICATIONID, CATEGORY, SENTIMENT, LOCATION, DISTANCE_METER, LONG, LAT, LONG_LAT_GEO, "similarity" FROM "GEO_COORD"
WHERE "DISTANCE_METER" < ${distance}
ORDER BY "similarity", "DISTANCE_METER" ASC;`

Taking into account the documentation here and the specific below paragraph, the provided code in the examples seem to be dangerous as unsanitized user input is used directly to concatenate a SQL string without using proper cds.ql.

Please verify if this is correct interpretation of the security of the provided samples and whether this should be corrected throughout the samples.

image

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions