TIL

TIL

  • Docs
  • Contact
  • Feedback
  • Contribute

›Week 12

Getting Started

  • Introduction

Principles of Programming Languages

    Week 3

    • Pattern Matching and Recursive Data Types

    Week 8

    • Monads

Introduction to Databases

    Week 3

    • Cinema Database

    Week 4

    • Pine Valley Furniture
    • Pine Valley Furniture - Solution

    Week 5

    • ERD to Shorthand Conversions
    • ERD to Shorthand Conversions - Solution

    Week 6

    • Normalizations
    • Normalizations - Solutions

    Week 7

    • Other Normal Forms
    • Other Normal Forms - Solutions

    Week 8

    • SQL Introduction

    Week 9

    • More SQL

    Week 10

    • Joins and Subqueries

    Week 11

    • Functions, Procedures, Triggers and Embedded SQL

    Week 12

    • Prepared SQL Statements
Edit

Prepared SQL Statements

Introduction

You may have learned about SQL Injection attacks, a common exploit a user could perform to gain access to your database. The main reason this happens is due to the lack of sanitizing user inputs. If you build your query insecurely (i.e., concatenating inputs) then you're making your application vulnerable to these types of attacks

Mitigation

To mitigate SQL Injections, you need to sanitize your inputs. There are multiple means to approach this, but the method relevant to your project is through Prepared SQL Statements.

Prepared Statements Syntax (Java)

import java.sql.*;

public class PreparedStatementExample
{
  public static void main(String[] args)
  {
    try
    {
      // create a mysql database connection
      // ...

      // the mysql insert statement
      String query = "INSERT INTO users (first_name, last_name, date_created, is_admin, num_points)"
        + " VALUES (?, ?, ?, ?, ?)";

      // set the parameters
      PreparedStatement preparedStmt = conn.prepareStatement(query);
      preparedStmt.setString (1, "Barney");
      preparedStmt.setString (2, "Rubble");
      preparedStmt.setDate   (3, startDate);
      preparedStmt.setBoolean(4, false);
      preparedStmt.setInt    (5, 5000);

      // execute the preparedstatement
      preparedStmt.execute();
      
      conn.close();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception!");
      System.err.println(e.getMessage());
    }
  }
}
Last updated on 1/4/2019
← Functions, Procedures, Triggers and Embedded SQL
  • Introduction
  • Mitigation
  • Prepared Statements Syntax (Java)
TIL
Docs
Getting StartedPrinciples of Programming Languages (C24)Introduction to Databases (C43)
Community
PiazzaQuercus
More
GitHubStar
Facebook Open Source
Copyright © 2019 Rakin Uddin