Teaching CxQL About a Home-Grown SQL Wrapper
Your company wraps cryptography and SQL in shared libraries. CryptoUtil.encrypt(String plaintext, String key) uses AES/ECB/PKCS5Padding; DbHelper.query(String sql) executes raw SQL. A custom query is needed because CxSAST does not flag data reaching DbHelper.query (the engine does not know it is a database sink), and it reports 400 SQL injection results inside src/test/java from fixtures that build SQL strings. There are 2,800 projects using the library, 40 of them internet-facing. The query team has CxAudit, one week, and a constraint that scan times must not grow by more than 10%. Existing Checkmarx Default scans take about 40 minutes per project. Developers already ignore the test-folder results, and one team has started excluding src/test globally at the project level, which the security team has not approved.
1. Write the core CxQL logic for a query that finds untrusted data reaching DbHelper.query, including sources, sinks, sanitizers, and test exclusion. Explain each decision.
Read the worked answer
Core query (CxQL, Java):
CxList sources = Find_Inputs();
CxList sinks = Find_Methods().FindByMemberAccess("DbHelper.query");
CxList sanitizers = Find_Sanitize();
CxList flows = sources.InfluencingOnAndNotSanitized(sinks, sanitizers);
CxList testFiles = All.FindByFileName("*src/test*");
result = flows - flows.GetByAncs(testFiles);
Reasoning: FindByMemberAccess targets the exact wrapper method rather than every method named query. Find_Inputs supplies the framework inputs such as servlet parameters; if your environment needs custom sources, union them in. InfluencingOnAndNotSanitized follows data flow through assignments, string concatenation, and helper returns, while excluding values cleaned by sanitizers already modeled in the engine. The test exclusion is a path filter; keep it narrow and confirm the test tree is not packaged in the deployed artifact.
If DbHelper has an overload that binds parameters, restrict the sink to the one-argument raw-SQL overload or you will flag safe calls. Keep the query ID stable so result history and triage survive updates. Also consider whether test fixtures are ever compiled into production; if they are, do not exclude them in the shared query.
2. How would you validate the query and roll it out to 2,800 projects without a regression or a scan-time problem?
Read the worked answer
Build a labeled corpus of at least 40 flows across five representative projects: true positives such as a request parameter or header reaching DbHelper.query through concatenation, and false positives such as constant SQL, enum-derived values, and already parameterized calls. Run the query in CxAudit on the largest project and record execution time. The constraint is under 10% of a 40-minute scan, so the query itself should complete well under a minute at project scale.
Compare precision and recall against the corpus and inspect every disagreement. Pilot on 20 projects in a company-named custom preset; do not edit Checkmarx Default, which can be overwritten during upgrades. Publish results to the pilot teams for a week, collect disputes, then roll out.
Keep a versioned copy of the query and document its semantics. Re-scan one pilot project after rollout to confirm result counts and scan durations match the CxAudit prediction. Finally, decide the test-folder policy centrally: a query-level exclusion is consistent across 2,800 projects, while individual project excludes are not; the security team should approve the policy in writing.
3. How would you write a second query for hardcoded keys passed to CryptoUtil.encrypt, and why should the wrapper not be flagged per call?
Read the worked answer
Flag the wrapper once and the risky call sites separately. A query for hardcoded keys targets calls to CryptoUtil.encrypt, takes argument index 1 (the key), and intersects it with string literals or static final constants:
CxList calls = Find_Methods().FindByMemberAccess("CryptoUtil.encrypt");
CxList keyArg = calls.GetArgument(1);
result = keyArg * Find_Strings();
Use the intersection carefully: if the key is passed as a parameter from configuration, trace to the configuration source rather than flagging the parameter itself. That produces per-call findings with fix locations at the callers, which is where the key must change.
Separately, one finding against the wrapper implementation for ECB mode is enough; it is a design defect in one place, not 700 duplicate findings. Do not treat CryptoUtil.encrypt as a sanitizer for SQL, and do not use the SQL query to find crypto issues; the libraries are independent. This split keeps the result set actionable and routes the ECB finding to the crypto owner rather than every feature team.