GuardRails

GuardRails

  • Languages iconEnglish
    • 中文

›Java

Overview

  • Introduction
  • What is GuardRails
  • Getting started
  • Findings
  • Vulnerabilities
  • Configuration
  • Custom Engines
  • False Positives
  • Enforce Passing Checks
  • Build Status on Pull Requests
  • FAQ
  • Pricing
  • Glossary
  • Tools & Licenses

API

  • Usage Guide
  • Endpoints

Vulnerabilities

  • Introduction
  • General

    • Overview
    • Hard-Coded Secrets

    Apex

    • Overview
    • Insecure Access Control
    • Insecure Network Communication
    • Insecure Processing of Data
    • Insecure Use of Cryptography
    • Insecure Use of Language/Framework API
    • Insecure Use of SQL Queries

    C/C++

    • Overview
    • Insecure Access Control
    • Insecure File Management
    • Insecure Processing of Data
    • Insecure Use of Cryptography
    • Insecure Use of Dangerous Function

    Dotnet

    • Overview
    • Insecure Access Control
    • Insecure Configuration
    • Insecure File Management
    • Insecure Processing of Data
    • Insecure Use of Cryptography
    • Insecure Use of Dangerous Function
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Elixir

    • Overview
    • Insecure Configuration
    • Insecure File Management
    • Insecure Processing of Data
    • Insecure Network Communication
    • Insecure Use of Dangerous Function
    • Insecure Use of Language/Framework API
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Go

    • Overview
    • Insecure File Management
    • Insecure Network Communication
    • Insecure Processing of Data
    • Insecure Use of Cryptography
    • Insecure Use of Dangerous Function
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Java

    • Overview
    • Using Vulnerable Libraries
    • Insecure Use of SQL Queries
    • Insecure Use of Dangerous Function
    • Insecure Use of Regular Expressions
    • Insecure Authentication
    • Insecure Configuration
    • Insecure File Management
    • Insecure Use of Cryptography
    • Insecure Use of Language/Framework API
    • Insecure Processing of Data
    • Insecure Network Communication

    Javascript/TypeScript

    • Overview
    • Insecure Authentication
    • Insecure Processing of Data
    • Insecure Use of SQL Queries
    • Insecure Use of Regular Expressions
    • Insecure Use of Language/Framework API
    • Insecure Use of Dangerous Function
    • Using Vulnerable Libraries

    Kubernetes

    • Overview
    • Insecure Access Control
    • Insecure Configuration
    • Insecure Network Communication

    PHP

    • Overview
    • Insecure Configuration
    • Insecure File Management
    • Insecure Network Communication
    • Insecure Processing of Data
    • Insecure Use of Dangerous Function
    • Insecure Use of Language/Framework API
    • Insecure Use of Regular Expressions
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Python

    • Overview
    • Insecure Configuration
    • Insecure Use of Cryptography
    • Insecure Network Communication
    • Insecure Processing of Data
    • Insecure Use of Dangerous Function
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Ruby

    • Overview
    • Insecure Access Control
    • Insecure Configuration
    • Insecure File Management
    • Insecure Network Communication
    • Insecure Processing of Data
    • Insecure Use of Dangerous Function
    • Insecure Use of Language/Framework API
    • Insecure Use of Regular Expressions
    • Insecure Use of SQL Queries
    • Using Vulnerable Libraries

    Rust

    • Overview
    • Using Vulnerable Libraries

    Solidity

    • Overview
    • Insecure Integer Arithmetic
    • Insecure Use of Low-Level Call
    • Reliance on Insecure Random Numbers
    • State Change After External Call
    • Transaction Order Dependence
    • Unprotected Critical Function
    • Use of Insecure Function
    • Dependence on Predictable Environment Variables
    • Write to Arbitrary Storage Location
    • Call to Untrusted Contract

    Terraform

    • Overview
    • Hard-Coded Secrets
    • Insecure Access Control
    • Insecure Configuration
    • Insecure Network Communication
    • Insecure Use of Cryptography

Insecure Use of SQL Queries

Why is this important?

SQL injections are dangerous because they can be easily identified by attackers. Hackers can use SQL injections to read from and sometimes even write to your database. SQL injections are very common and have been the cause of many high-profile breaches.

Check out this video for a high-level explanation:

SQL Injection Explanation Video

This is a biggie, read below to find out how to fix it.

Fixing Insecure Use of SQL Queries

Option A: Use Prepared Statements Securely

  1. Go through the issues that GuardRails identified in the PR.
  2. Look for insecure patterns like this:
  // Persistence Manager
  PersistenceManager pm = getPM();
  Query q = pm.newQuery("select * from Users where name = " + input);
  q.execute();

or:

  // Entity Manager
  EntityManager pm = getEM();
  TypedQuery<UserEntity> q = em.createQuery(
      String.format("select * from Users where name = %s", username),
      UserEntity.class);
  UserEntity res = q.getSingleResult();

or:

  //JDBC Template
  JdbcTemplate jdbc = new JdbcTemplate();
  int count = jdbc.queryForObject("select count(*) from Users where name = '"+paramName+"'", Integer.class);

or:

  // JDBC Injection
  Connection conn = [...];
  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("update COFFEES set SALES = "+nbSales+" where COF_NAME = '"+coffeeName+"'");

or:

  //Scala Slick Injection
  db.run {
   sql"select * from people where name = '#$value'".as[Person]
  }

or:

  //Scala Anorm Injection
  val peopleParser = Macro.parser[Person]("id", "name", "age")
  DB.withConnection { implicit c =>
    val people: List[Person] = SQL("select * from people where name = '" + value + "'").as(peopleParser.*)
  }

or:

  //Android
  String query = "SELECT * FROM  messages WHERE uid= '"+userInput+"'" ;
  Cursor cursor = this.getReadableDatabase().rawQuery(query,null);
  1. Replace it with the following:
  // Persistence Manager
  PersistenceManager pm = getPM();
  Query q = pm.newQuery("select * from Users where name = nameParam");
  q.declareParameters("String nameParam");
  q.execute(input);

or:

  // Entity Manager
  TypedQuery<UserEntity> q = em.createQuery(
  "select * from Users where name = usernameParam",UserEntity.class)
  .setParameter("usernameParam", username);
  UserEntity res = q.getSingleResult();<

or:

  //JDBC Template
  JdbcTemplate jdbc = new JdbcTemplate();
  int count = jdbc.queryForObject("select count(*) from Users where name = ?", Integer.class, paramName);

or:

  // JDBC Injection
  Connection conn = [...];
  conn.prepareStatement("update COFFEES set SALES = ? where COF_NAME = ?");
  updateSales.setInt(1, nbSales);
  updateSales.setString(2, coffeeName);

or:

  //Scala Slick Injection
  db.run {
   sql"select * from people where name = $value".as[Person]
  }

or:

  //Scala Anorm Injection
  val peopleParser = Macro.parser[Person]("id",  "name", "age")
  DB.withConnection { implicit c =>
    val people: List[Person] = SQL"select * from people where name = $value".as(peopleParser.*)
  }

or:

  //Android
  String query = "SELECT * FROM  messages WHERE uid= ?" ;
  Cursor cursor = this.getReadableDatabase().rawQuery(query,new String[] {userInput});
  1. Test it
  2. Ship it 🚢 and relax 🌴

Option B: Use Hibernate Securely

  1. Go through the issues that GuardRails identified in the PR.
  2. Look for insecure patterns like this:
  Session session = sessionFactory.openSession();
  Query q = session.createQuery("select t from UserEntity t where id = " + input);
  q.execute();
  1. Replace it with the following:
  Session session = sessionFactory.openSession();
  Query q = session.createQuery("select t from UserEntity t where id = :userId");
  q.setString("userId",input);
  q.execute();

or by using Hibernate Criteria:

  Session session = sessionFactory.openSession();
  Query q = session.createCriteria(UserEntity.class)
    .add( Restrictions.like("id", input) )
    .list();
  q.execute();
  1. Test it
  2. Ship it 🚢 and relax 🌴

Option C: Use the ESAPI SQL Encoder for Special SQL Characters

  1. Go through the issues that GuardRails identified in the PR.
  2. Look for insecure patterns like this:
  // EntityManager.createQuery
  createQuery("select * from User where id = '"+inputId+"'");

or:

  //Turbine API DSL
  List<Record> BasePeer.executeQuery( "select * from Customer where id=" + inputId );

or:

  //AWS Query Injection
  String customerID = getAuthenticatedCustomerID(customerName, customerCredentials);
  String productCategory = request.getParameter("productCategory");
  ...
  AmazonSimpleDBClient sdbc = new AmazonSimpleDBClient(appAWSCredentials);
  String query = "select * from invoices where productCategory = '"
              + productCategory + "' and customerID = '"
              + customerID + "' order by '"
              + sortColumn + "' asc";
  SelectResult sdbResult = sdbc.select(new SelectRequest(query));
  1. Leverage ESAPI for SQL encoding.
  // EntityManager.createQuery
  import org.owasp.esapi.Encoder;
  createQuery("select * from User where id = '"+Encoder.encodeForSQL(inputId)+"'");

or:

  //Turbine API DSL
  import org.owasp.esapi.Encoder;
  BasePeer.executeQuery("select * from Customer where id = '"+Encoder.encodeForSQL(inputId)+"'");

or:

  //AWS Query Injection
  import org.owasp.esapi.Encoder;
  String customerID = getAuthenticatedCustomerID(customerName, customerCredentials);
  String productCategory = request.getParameter("productCategory");
  ...
  AmazonSimpleDBClient sdbc = new AmazonSimpleDBClient(appAWSCredentials);
  String query = "select * from invoices where productCategory = '"
              + Encoder.encodeForSQL(productCategory) + "' and customerID = '"
              + Encoder.encodeForSQL(customerID) + "' order by '"
              + Encoder.encodeForSQL(sortColumn) + "' asc";
  SelectResult sdbResult = sdbc.select(new SelectRequest(query));
  1. Test it
  2. Ship it 🚢 and relax 🌴

More information

  • SQL Injection Prevention Cheat Sheet
  • CAPEC-66: SQL Injection
  • Common Weakness Enumeration (CWE-89)
  • Fixing SQL Injection Vulnerabilities
  • Hibernate Documentation: Query Criteria
  • Hibernate Javadoc: Query Object
  • HQL for pentesters
  • JDO: Object Retrieval
  • InformIT.com: Practical Advice for Building Secure Android Databases in SQLite
  • Packtpub.com: Knowing the SQL-injection attacks and securing our Android applications from them
  • Android Database Support (Enterprise Android: Programming Android Database Applications for the Enterprise)
← Using Vulnerable LibrariesInsecure Use of Dangerous Function →
  • Why is this important?
  • Fixing Insecure Use of SQL Queries
    • Option A: Use Prepared Statements Securely
    • Option B: Use Hibernate Securely
    • Option C: Use the ESAPI SQL Encoder for Special SQL Characters
  • More information
  • Status
  • Help
  • Security
  • Terms
  • Privacy

© 2021 GuardRails