sql-injection

Try in Playground
python-securitySecurityCritical

0

injectionmysql
CWE-89

This rule checks that the user does not use any formatting code that would trigger a SQL injection.

This rule works for both MySQL and PostgreSQL.

See also the rule that detects variables using SQL statements.

It also covers RAW SQL insertion for SQL Alchemy.

** Learn More **

  • CWE-89

Ast Rule: function call


sql-injection

How to write a rule
function visit(node) {
  // no arguments
  if (!node.arguments || !node.context || !node.arguments.values || node.arguments.values.length == 0){
    return;
  }
  
  const arguments = node.arguments.values;
  const nbArguments = node.arguments.values.length;

  // no function or module name
  if (!node.functionName){
    return;
  }
  

  // common names given for cursor to execute SQL
  const objects = ["cursor", "cur"];
  const argumentValue = node.arguments.values[0].value.value;

  const useFstring = argumentValue.startsWith("f");
  const useFormatString = argumentValue.includes(".format(");
  const hasError = useFstring || useFormatString;
  
  
  var errorMessage = "Do not use f-string in SQL queries, it leads to SQL injections";

  if (useFormatString) {
    errorMessage = "Do not use format string in SQL queries, it leads to SQL injections";
  }
  
  if(node.moduleOrObject && objects.includes(node.moduleOrObject.value)) {
  	// check that we do not have cursor.execute() or cur.execute() with a potential buggy SQL query
    objects.forEach(o => {
      if(hasError && node.functionName.value === "execute" && node.moduleOrObject && node.moduleOrObject.value === o){

        const error = buildError(node.start.line, node.start.col, node.end.line, node.end.col, errorMessage, "CRITICAL", "SAFETY");
        addError(error);
      }
    });
  }
  
  
  if (node.functionName && node.functionName.value === "text" ) {
    // check for SQL Alchemy
    const importTextFromSqlAlchemy = node.context.imports
              .filter(r => r.pkg && r.pkg.value === "sqlalchemy" && r.elements && 
              r.elements.filter(e => e.name).map(e => e.name.value).includes("text")).length > 0;

    if (hasError && importTextFromSqlAlchemy && node.functionName.value === "text") {
        const error = buildError(node.start.line, node.start.col, node.end.line, node.end.col, errorMessage, "CRITICAL", "SAFETY");
        addError(error);
    }
  }
}

sql-alchemy-errors.py

Expected test result: has error

from sqlalchemy import text
  
# write the SQL query inside the text() block
sql = text(f"SELECT * from BOOKS WHERE BOOKS.book_price > {my_price}")
results = engine.execute(sql)

sql-alchemy-no-error.py

Expected test result: no error

from sqlalchemy import text
  
# write the SQL query inside the text() block
sql = text('SELECT * from BOOKS WHERE BOOKS.book_price > 50')
results = engine.execute(sql)

format-string.py

Expected test result: has error

use of a format string



cursor.execute("SELECT * FROM users WHERE username = '{0}'".format(username));

no-f-string.py

Expected test result: no error

No use of f-string

mysql-injection-with-fstring.py

Expected test result: has error

Any user can insert data in username.

Add comment

Log in to add a comment


    Be the first one to leave a comment!

Codiga Logo
Codiga Hub
  • Rulesets
  • Explore
  • Cookbooks
  • Playground
soc-2 icon

We are SOC-2 Compliance Certified

G2 high performer medal

Codiga – All rights reserved 2022.