Issues with testing env(input) and Pythonic SQLi [CodeShield]

[njmennona]

Hello:
I am learning how to best use CodeShield. I have been trying to use CodeShield to detect SQLi (SQL Injections). I haven't been able to get CodeShield to use its rules to detect any Pythonic SQLi To clarify my issues, after writing my own SQLi, I inserted examples from the code blocks found in: CodeShield/insecure_code_detector/rules/semgrep/python/sql_injection_cursor_execute.py into the str llm_output_code found in CodeShield/notebook/CodeShieldUsageDemo.ipynb in place of the md5 insecurity (Example 1 - Simple hard-coded example). I want to emphasize, however, that running semgrep in the command line (using --config sql_injection_cursor_execute.yaml) for that .py file correctly identifies all of the SQLi.

After these difficulties with SQLi, I ran the testing file found in: CodeShield/tests/test_codeshield.py. When I ran this, the output of scan_code for the input: ("eval(input)", True) evaluates to False for scan_results.is_insecure, i.e. scan_results = await CodeShield.scan_code(test_input)->False for test_input := "eval(input)". When I modified test_codeshield.py to include the original Example 1 md5 insecurity: """def hashString(input):
return hashlib.md5(input)""", scan_code produced the correct output (scan_results = True).

In summary, I cannot get CodeShield to identify vulnerabilities outside of the narrow example case provided in the Usage Demo (md5 error). I have used both CodeShieldUsageDemo.ipynb and test_codeshield.py to arrive at this conclusion; I have used examples that are included in CodeShield's documentation.

Please let me know if there is something I am misunderstanding or doing incorrectly. I appreciate any time devoted to this issue, and your patience in taking the time to understand my difficulties. Please let me know if any part of my explanation is unclear. Thank you.

[BGAK2024]

Here are a few possibilities and things to consider:

• Code Context in the Notebook: When you paste the SQL injection examples directly into the llm_output_code string in the notebook, are they being treated as a single string literal? CodeShield and Semgrep rely on parsing the code to understand its structure. If the SQL injection examples are embedded within a larger string without the necessary Python code context (like variable assignments, function definitions, etc.), the Semgrep rules might not be able to properly analyze them as executable Python code.
  For example, if you have something like:
  llm_output_code = "cursor.execute('SELECT * FROM users WHERE username = '" + user_input + "';"

  CodeShield needs to recognize cursor.execute and the structure of the SQL query with the potentially injected user_input. Simply having the SQL string within a larger string might not be enough for the rule to trigger within the CodeShield framework in the notebook.

• Rule Configuration within CodeShield: While the Semgrep configuration file (sql_injection_cursor_execute.yaml) works on the command line, it's possible that CodeShield's internal usage of Semgrep might have slightly different configurations or might be applying rules in a specific way. It's worth considering if CodeShield loads and applies all Semgrep rules in the same way as the command-line tool with the --config flag.

• eval(input) Test Case: The fact that scan_code("eval(input)", True) returns False for is_insecure is indeed unexpected, as eval() with user-controlled input is a classic security vulnerability. This might indicate a more general issue with how CodeShield is evaluating certain types of dynamic code execution risks. The md5 example is static code, which might be why it's being detected.
  Here are a few things you could try to further diagnose the issue:

• Modify the Notebook Example: Instead of just pasting the SQL injection snippets into the string, try to create a minimal but complete Python code snippet within llm_output_code that demonstrates the vulnerability. For example:
  llm_output_code = """
  import sqlite3

def query_database(user_input):
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
username = user_input
query = "SELECT * FROM users WHERE username = '" + username + "';"
cursor.execute(query)
results = cursor.fetchall()
conn.close()
return results

user_provided_input = "'; DROP TABLE users; --"
query_database(user_provided_input)
"""

This provides a more complete context for CodeShield to analyze.

• Examine CodeShield's Output: When you run the scan_code function in the notebook, what is the full scan_results object? Does it contain any information about the rules that were evaluated or any errors that occurred? This might give us a clue about why the SQL injection rules aren't being triggered.
• Review CodeShield's Code: If you're comfortable doing so, you might want to take a look at the CodeShield.scan_code implementation to see how it loads and applies the Semgrep rules. This could reveal if there are any specific configurations or limitations in how it uses Semgrep internally.
  Regarding the eval(input) issue, it definitely seems like something that CodeShield should flag as insecure. It might be worth reporting this as a potential bug or limitation if we can't find a configuration issue on your end.
  Don't worry, you're asking all the right questions and providing clear details about what you've tried. We'll work through this! Let me know the results of trying the modified notebook example or if you have any more information about the scan_results output.

[njmennona]

Hello @BGAK2024:

Thank you for your detailed and thoughtful response. I think I can answer all of these while providing more background. For disclosure, here is the full code I was using to investigate the SQLi issue:

from codeshield.cs import CodeShield
import asyncio
import hashlib


async def scan_llm_output(llm_output_code):
    result = await CodeShield.scan_code(llm_output_code)
    if result.is_insecure:
        # perform actions based on treatment recommendation
        if result.recommended_treatment == "block":
            llm_output_code = "*** Code Security issues found, blocking the code ***"
        if result.recommended_treatment == "warn":
            llm_output_code = (
                llm_output_code
                + "*** Warning: The generated snippet contains insecure code ***"
            )
    summary = "Security issue detected" if result.is_insecure else "No issues found"

    print("## LLM output after treatment")
    print("\t %s \n" % llm_output_code)

    print("## Results:\n")
    print("\t %s" % (summary))
    print("\t Recommended treatment: %s\n" % result.recommended_treatment)

    print("## Details:\n")
    if result.issues_found is not None and len(result.issues_found) > 0:
        issue = result.issues_found[0]
        print(
            "\tIssue found: \n\t\tPattern id: %s \n\t\tDescription: %s \n\t\tSeverity: %s \n\t\tLine number: %s"
            % (issue.pattern_id, issue.description, issue.severity, issue.line)
        )


async def main():
    llm_output_code = """
        import sqlite3

        def query_database(user_input):
        conn = sqlite3.connect(':memory:')
        cursor = conn.cursor()
        username = user_input
        query = "SELECT * FROM users WHERE username = '" + username + "';"
        cursor.execute(query)
        results = cursor.fetchall()
        conn.close()
        return results

        user_provided_input = "'; DROP TABLE users; --"
        query_database(user_provided_input)
    """
    # import sqlite3

    # def get_user_data(user_id):
    #     connection = sqlite3.connect("users.db")
    #     cursor = connection.cursor()
    #     user_input = "1 OR 1=1"
    #     query = f"SELECT * FROM users WHERE user_id = {user_input}"
    #     cursor.execute(query)
    #     return cursor.fetchall()
    # """

    # llm_output_code = 'eval(input)'


    await scan_llm_output(llm_output_code)


if __name__ == "__main__":
    asyncio.run(main())
# llm_output_code = """
# import hashlib
# def hashString(input):
#     return hashlib.md5(input)
# """
# llm_output_code = """
# import sqlachemy
# import pandas as pd
# def sql_qeury(userName,ItemName):
#     engine = sqlalchemy.create_engine("postgreqsql://tony:tiger@localhost")

#     query=f"SELECT * FROM items WHERE owner = '"
#                     + '{userName}' + "' AND itemname = '"
#                     + '{ItemName}' + "'";
#     return pd.read_sql(query,engine)
# """

I included the commented out pandas read_sql function because initially when I was investigating I was trying to identify the SQL command itself. It was only when I was debugging the md5 issue that I found the semgrep rules for python and realized that the cursor.execute is necessary for the identification of these SQLi. Not the biggest deal since I thought I can modify the rules myself to pick up Pandas queries as well. Notice I included your provided SQLi as well as the commented out SQLi taken from the rules embedded in CodeShield's package (which is the SQLi test I ran before opening up this issue).

As for the semgrep --config command, the reason I brought that up was to illustrate that the rules themselves for semgrep (as I verified) are not incorrect as they do indeed work independently of CodeShield. But, if I am using a semgrep wrapper for string literals (aka CodeShield) specifically for LLM output, then it suggests there is a bug in CodeShield itself. Let me know if my conclusion is explained well here.

Finally, in terms of the md5 and eval(input) issue; in debugging, I first debugged the md5 insecurity and understood how CodeShield itself works. My current understanding (please correct me if I'm wrong) is that CodeShield takes the string literal, opens up a temp-file that this string is then pasted in to create a temp code file for all languages that are supported by CodeShield (e.g. a temp .php,.py, etc). Then the semgrep rules are applied (or analyzed, however CodeShield describes them) for each language supported for all of the rules particular to that language. In the case of md5, multiple languages are detected (e.g. javascript, php, and python), and the insecurities are raised in the issue list (for which len(issues)>0 causes the issues to be raised). So, for the md5 issue, the rules are captured--for eval(input) and our SQLi, no issues are detected--the output of scan_results.

The reason I investigated eval(input) is that is a simpler test of CodeShield than SQLi, and since that failed I thought I had to raise this issue as then I think there has to be a bug somewhere. Especially since the md5 issue works perfectly for CodeShield. I'll keep investigating, but for now this is where I'm at.

Please let me know if this provides more clarity, or if further explanation/investigations are needed.