T09 · Insecure Skill Coding Practices
- Location
scripts/hybrid_search_client.py:80- Finding
SQL Injection Through Unvalidated Table Names and Query Limits
- Content
View full analysis
None: exists_clause = "IF NOT EXISTS" if if_not_exists else "" vec_dimensions = self.embedding_client.dimensions create_sql = f""" CREATE TABLE {exists_clause} {table_name} ( `doc_id` UInt64, `title` String, `content` String, `embedding` Array(Float32), `create_time` DateTime DEFAULT now(), INDEX content_idx content TYPE inverted('standard', '{{\"version\":\"v2\"}}') GRANULARITY 1, INDEX embedding_idx embedding TYPE HNSW_SQ('DIM={vec_dimensions}', 'metric=COSINE', 'M=32', 'EF_CONSTRUCTION=256') GRANULARITY 1 ) ENGINE = MergeTree() ORDER BY doc_id SETTINGS index_granularity = 1024 """ self.client.command(create_sql) ``` ```python insert_sql = f""" INSERT INTO {table_name} (doc_id, title, content, embedding) VALUES (%s, %s, %s, %s) """ self.client.command(insert_sql, parameters=[doc_id, title, content, embedding]) ``` ```python search_sql = f""" SELECT doc_id, title, content, _text_search_score as bm25_score FROM {table_name} WHERE textSearch(content, %s) ORDER BY bm25_score DESC LIMIT {top_k} """ results = self.client.query(search_sql, parameters=[query]).result_rows ``` ```python search_sql = f""" SELECT doc_id, title, content, cosineDistance(embedding, %s) as vector_score FROM {table_name} ORDER BY vector_score ASC LIMIT {top_k} """ results = self.client.query(search_sql, parameters=[query_embedding]).result_rows ``` ### Technical Analysis The public database methods interpolate `table_name` and `top_k` directly into SQL strings. Although document values, search text, and embeddings are passed thro ...[truncated 2293 chars]- Remediation
View remediation
str: if not isinstance(value, str) or not _IDENTIFIER.fullmatch(value): raise ValueError("Invalid SQL identifier") return value ``` 2. If qualified identifiers are required, validate each database and table component independently rather than allowing arbitrary punctuation. 3. Use the database driver's supported identifier-quoting mechanism after validation. Do not treat ordinary value parameterization as protection for table names. 4. Enforce integer types and conservative bounds for all ranking parameters: ```python def validate_limit(value: int, maximum: int = 1000) -> int: if isinstance(value, bool) or not isinstance(value, int): raise TypeError("Limit must be an integer") if value < 1 or value > maximum: raise ValueError("Limit is outside the permitted range") return value ``` 5. Apply equivalent validation to `top_k`, `recall_k`, and `rrf_k`. 6. Prefer mapping externally supplied logical index names to server-side table names controlled by the application. 7. Run the client under a least-privileged ByteHouse account. Separate DDL privileges from search and insertion privileges where possible. 8. Add negative tests containing quotes, whitespace, comments, delimiters, Unicode lookalikes, and oversized or negative limit values. ]]>
