variable-sql-statement-injection

Try in Playground
python-securityUnknownCritical

0

No tags

CWE-89

Check for declarations of variables for a SQL statement where we have potential SQL injections.

Ast Rule: assignment


variable-sql-statement-injection

How to write a rule
const hasPotentialInjection = (query) => {
  const formatRegExp = /\{[a-zA-Z0-9]*\}/i;
  if (query.match(formatRegExp)) {
    return true;
  }
  return false;
};

const isSqlQuery = (randomString) => {
  const selectRegexp = /.*\s*SELECT\s+[a-zA-Z0-9,*\.\_\-]+\s+FROM\s+[a-zA-Z0-9,*\.\_]/i;
  if (randomString.match(selectRegexp)) {
    return true;
  }
  const updateRegexp = /.*\s*UPDATE\s+[a-zA-Z0-9\_\-]+\s+SET/i;
  if (randomString.match(updateRegexp)) {
    return true;
  }
  const deleteRegexp = /.*\s*DELETE\s+FROM\s+[a-zA-Z0-9,*\.\_\-]+/i;
  if (randomString.match(deleteRegexp)) {
    return true;
  }
  return false;
};

function visit(node, filename, code) {
  
  if (filename.includes("_test.py") || filename.startsWith("test_")) {
  	return;
	}
  
  if(!node.right) {
    return;
  }

  
  if(node.right.astType === "string") {
    const nodeString = node.right;
    const strValue = nodeString.value.toLowerCase();
    if(!strValue.includes("select") &&
       !strValue.includes("update") &&
       !strValue.includes("delete")) {
      return;
    }
    if(isSqlQuery(nodeString.value) && hasPotentialInjection(nodeString.value)){
      const error = buildError(nodeString.start.line, nodeString.start.col, 
                               nodeString.end.line, nodeString.end.col, 
                               "potential SQL injection", "WARNING", "SECURITY");
      addError(error);

    }
  }
  
  if(node.right.astType === "functioncall") {
    const functionCall = node.right;
    if (!functionCall.moduleOrObject) {
      return;
    }
    if(functionCall.functionName.value === "format") {
      if(isSqlQuery(functionCall.moduleOrObject.value) && hasPotentialInjection(functionCall.moduleOrObject.value)){
        	const error = buildError(functionCall.moduleOrObject.start.line, functionCall.moduleOrObject.start.col, 
                  								 functionCall.moduleOrObject.end.line, functionCall.moduleOrObject.end.col, 
                   								 "potential SQL injection", "WARNING", "SECURITY");
          addError(error);

      }
    }
  }
}

update-statement-format.py

Expected test result: has error

stmt = "UPDATE analysis_results SET running_time_sec='{0}' WHERE id={1}".format(nsec, id)

delete-format.py

Expected test result: has error

v = "UPDATE bar SET plop={0}".format(plip)

update.py

Expected test result: has error

v = "UPDATE bar SET plop={0}".format(plip)

delete.py

Expected test result: has error

query-fstring.py

Expected test result: has error

query-string.py

Expected test result: has error

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.