T09 · Insecure Skill Coding Practices
- Location
scripts/content-analysis.js:147- Finding
SQL Injection Through Unescaped Upstream Identifiers
- Content
View full analysis
Vulnerability Details
File Location:
scripts/content-analysis.js, lines 147–184, 294–308, and 443–449
Vulnerability Type: SQL injection caused by dynamic construction ofINclauses
Risk Level: MediumVulnerable Code
js // Lines 147–166 const activeIds = activeMarkets.map(r => `'${r.market_id}'`).join(','); const buys = await mcp.queryWithRetry(` SELECT market_id, wallet_address, price AS buy_price, usd_amount AS trade_size, traded_at FROM trades WHERE traded_at >= now() - INTERVAL 24 HOUR AND type = 'trade' AND side = 'buy' AND outcome_index = 0 AND price <= ${C.max_buy_price} AND price > 0 AND usd_amount >= ${C.min_trade_usd} AND market_id IN (${activeIds}) AND wallet_address IS NOT NULL AND wallet_address != '' ORDER BY usd_amount DESC LIMIT 3000 `, { maxRows: 3000, retries: 3 });js // Lines 178–186 const walletIn = walletSet.map(w => `'${w}'`).join(','); const walletCounts = await mcp.queryWithRetry(` SELECT wallet_address, count() AS total_trades FROM trades WHERE type = 'trade' AND wallet_address IN (${walletIn}) GROUP BY wallet_address `, { maxRows: 5000, retries: 3 });js // Lines 294–308 const bigIds = bigMarkets.map(r => `'${r.market_id}'`).join(','); const walletSides = await mcp.queryWithRetry(` SELECT market_id, wallet_address, sumIf(usd_amount, side = 'buy') AS buy_total, sumIf(usd_amount, side = 'sell') AS sell_total, count() AS trade_count FROM trades WHERE traded_at >= now() - INTERVAL 24 HOUR AND type = 'trade' AND market_id IN (${bigIds}) AND wallet_address IS NOT NULL AND usd_amount > 0 GROUP BY market_id, wallet_address HAVING buy_total >= ${C.min_wallet_usd} OR sell_total >= ${C.min_wallet_usd} ORDER BY market_id, greatest(buy_total, sell_total) DESC LIMIT 3000 `, { maxRows: 3000, retries: 3 });js // Lines 443–451 const inLi ...[truncated 2679 chars]- Remediation
View remediation
Remediation Suggestions
- Use parameterized array bindings. Pass identifier arrays through placeholders or typed query parameters supported by the MCP client instead of interpolating SQL fragments.
js const marketIds = activeMarkets.map(row => row.market_id); const buys = await mcp.queryWithRetry( ` SELECT market_id, wallet_address, price AS buy_price, usd_amount AS trade_size, traded_at FROM trades WHERE traded_at >= now() - INTERVAL 24 HOUR AND type = 'trade' AND side = 'buy' AND outcome_index = 0 AND market_id IN ({market_ids:Array(String)}) `, { params: { market_ids: marketIds }, maxRows: 3000, retries: 3, } );Adapt the parameter syntax to the actual interface exposed by
mcp-client.- Strictly validate external identifiers before reuse. Wallet addresses should match the exact expected format:
js const WALLET_RE = /^0x[0-9a-fA-F]{40}$/; function validateWallet(value) { if (typeof value !== 'string' || !WALLET_RE.test(value)) { throw new Error('Invalid wallet address returned by the data service'); } return value; }Define similarly restrictive patterns and length limits for
market_idandcondition_idbased on their documented canonical formats. Reject unexpected values rather than attempting to repair them.-
Centralize safe list-query construction. Create one helper that accepts validated values and uses parameter binding. Replace all four interpolation sites so future queries do not reintroduce the issue.
-
Fail closed and record safe diagnostics. If an upstream identifier is malformed, skip or reject the affected record and log only a sanitized diagnostic. Do not include the raw hostile value in SQL errors or user-facing reports.
-
Retain server-side restrictions. Continue enforcing SELECT-only queries, query timeouts, row limits, and least-privilege access on the MCP service. These controls reduce impact but should not repla ...[truncated 239 chars]