Rechercher
 

JDBC et les attaques par injection SQL

Web Filters Contrer les attaques XSS



Accès rapide :
La vidéo
Les codes de la vidéo
La classe d'accès à la table des utilisateurs
La classe d'accès à la table des articles
La classe DAOContext
La classe User
La classe Article
La classe CatalogBrowser
La classe ShoppingCartLine
La classe Login.java (le contrôleur du MVC utilisé pour la page login)
La classe ViewArticle.java (le contrôleur du MVC utilisé pour la page viewArticle)
La vue login
La vue viewArticle

La vidéo

Cette vidéo vous montre comment coder des pages Web en Java EE (servlets et JSP) qui se connectent à une base de données relationnelle (SQL) via l'API JDBC. J'y présente aussi des problématiques d'injections SQL que vous pouvez rencontrer si vos codes ne sont pas suffisamment robustes.


JDBC et les attaques par injection SQL

Les codes de la vidéo

La classe d'accès à la table des utilisateurs

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
package fr.koor.webstore.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import fr.koor.webstore.business.User;

public class UserDAO extends DAOContext {

    public static User isValidLogin( String login, String password ) {
        try ( Connection connection = DriverManager.getConnection( dbURL, dbLogin, dbPassword ) ) {
            //String strSql = "SELECT * FROM T_Users WHERE login='" 
                            + login + "' AND password='" + password + "'";
            String strSql = "SELECT * FROM T_Users WHERE login=? AND password=?";
            try ( PreparedStatement statement  = connection.prepareStatement( strSql ) ) {
                statement.setString( 1, login );
                statement.setString( 2, password );
                try ( ResultSet resultSet = statement.executeQuery() ) {
                    if ( resultSet.next() ) {
                        return new User(
                                resultSet.getInt( "idUser" ),
                                resultSet.getString( "login" ),
                                resultSet.getString( "password" ),
                                resultSet.getInt( "connectionNumber" )
                        );
                    } else {
                        return null;
                    }
                }
            }
        } catch ( Exception exception ) {
            throw new RuntimeException( exception );
        }
    }
    
}
Fichier UserDAO.java

La classe d'accès à la table des articles

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
 62 
 63 
 64 
 65 
 66 
 67 
 68 
 69 
 70 
 71 
 72 
 73 
 74 
 75 
package fr.koor.webstore.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import fr.koor.webstore.business.Article;

public class ArticleDAO extends DAOContext {

    public static int getArticleCount() {
        try ( Connection connection = DriverManager.getConnection( dbURL, dbLogin, dbPassword ) ){

            String strSql = "SELECT count(idArticle) FROM T_Articles";
            try ( Statement statement  = connection.createStatement() ) {
                try ( ResultSet resultSet = statement.executeQuery( strSql ) ) {
                    resultSet.next();
                    return resultSet.getInt( 1 );
                }
            }
            
        } catch ( Exception exception ) {
            
            throw new RuntimeException( exception );
            
        }
    }
    
    public static Article getArticleById( int idArticle ) {
        try ( Connection connection = DriverManager.getConnection( dbURL, dbLogin, dbPassword ) ){
            System.out.println( "connection to the database" );
            String strSql = "SELECT * FROM T_Articles WHERE idArticle=?";
            try ( PreparedStatement statement  = connection.prepareStatement( strSql ) ) {
                statement.setInt( 1, idArticle );
                try ( ResultSet resultSet = statement.executeQuery() ) {
                    resultSet.next();
                    return new Article(
                            resultSet.getInt( "idArticle" ),
                            resultSet.getString( "description" ),
                            resultSet.getString( "brand" ),
                            resultSet.getDouble( "unitaryPrice" )
                    );
                }
            }
            
        } catch ( Exception exception ) {
            
            throw new RuntimeException( exception );
            
        }
    }
    
    
    public static void updateArticle( Article article ) {
        try ( Connection connection = DriverManager.getConnection( dbURL, dbLogin, dbPassword ) ){

            String strSql = "UPDATE T_Articles SET description=?, brand=?, unitaryPrice=? WHERE idArticle=?";
            try ( PreparedStatement statement  = connection.prepareStatement( strSql ) ) {
                statement.setString( 1, article.getDescription() );
                statement.setString( 2, article.getBrand() );
                statement.setDouble( 3, article.getUnitaryPrice() );
                statement.setInt( 4, article.getIdArticle() );
                statement.executeUpdate();
            }
            
        } catch ( Exception exception ) {
            
            throw new RuntimeException( exception );
            
        }
    }
    
}
Fichier ArticleDAO.java

La classe DAOContext

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
package fr.koor.webstore.dao;

import javax.servlet.ServletContext;

public class DAOContext {
    
    protected static String dbURL;
    protected static String dbLogin;
    protected static String dbPassword;
    
    
    public static void init( ServletContext context ) {
        try {
            
            Class.forName( context.getInitParameter( "JDBC_DRIVER" ) );
            dbURL = context.getInitParameter( "JDBC_URL" );
            dbLogin = context.getInitParameter( "JDBC_LOGIN" );
            dbPassword = context.getInitParameter( "JDBC_PASSWORD" );
            
        } catch( Exception exception ) {
            
            exception.printStackTrace();
            
        }
    }
    
}
Fichier DAOContext.java

La classe User

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
package fr.koor.webstore.business;

public class User {

    private int idUser;
    private String login;
    private String password;
    private int connectionNumber;
    
    
    public User() {
        this( 0, "john", "doe", 0 );
    }
    
    public User( int idUser, String login, String password, int connectionNumber ) {
        this.setIdUser( idUser );
        this.setLogin( login );
        this.setPassword( password );
        this.setConnectionNumber( connectionNumber );
    }
    
    
    public int getIdUser() {
        return idUser;
    }
    
    public void setIdUser(int idUser) {
        this.idUser = idUser;
    }
    
    public String getLogin() {
        return login;
    }
    
    public void setLogin(String login) {
        this.login = login;
    }
    
    public String getPassword() {
        return password;
    }
    
    public void setPassword(String password) {
        this.password = password;
    }
    
    public int getConnectionNumber() {
        return connectionNumber;
    }
    
    public void setConnectionNumber(int connectionNumber) {
        this.connectionNumber = connectionNumber;
    }

    @Override
    public String toString() {
        return "User[idUser=" + idUser + ", login=" + login + ", password=" + 
               password + ", connectionNumber=" + connectionNumber + "]";
    }
    
}
La classe User

La classe Article

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
package fr.koor.webstore.business;

public class Article {

    private int idArticle;
    private String description;
    private String brand;
    private double unitaryPrice;
    
    
    public Article() {
        this( 0, "unknown", "unknown", 0 );
    }
    
    
    public Article( int idArticle, String description, String brand, double unitaryPrice ) {
        this.setIdArticle( idArticle );
        this.setDescription( description );
        this.setBrand( brand );
        this.setUnitaryPrice( unitaryPrice );
    }


    public int getIdArticle() {
        return idArticle;
    }
    
    public void setIdArticle(int idArticle) {
        this.idArticle = idArticle;
    }
    
    public String getDescription() {
        return description;
    }
    
    public void setDescription(String description) {
        this.description = description;
    }
    
    public String getBrand() {
        return brand;
    }
    
    public void setBrand(String brand) {
        this.brand = brand;
    }
    
    public double getUnitaryPrice() {
        return unitaryPrice;
    }
    
    public void setUnitaryPrice(double unitaryPrice) {
        this.unitaryPrice = unitaryPrice;
    }
    
    
    
}
La classe Article

La classe CatalogBrowser

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
package fr.koor.webstore.business;

import java.util.ArrayList;
import java.util.List;

import fr.koor.webstore.dao.ArticleDAO;

public class CatalogBrowser {

    private int currentPosition = 1;
    private Article currentArticle;
    private int articleCount = ArticleDAO.getArticleCount();
    
    private List<ShoppingCartLine> shoppingCart = new ArrayList<>();
    
    
    public CatalogBrowser() {
        currentArticle = ArticleDAO.getArticleById( currentPosition );
    }
    
    public Article getCurrentArticle() {
        return currentArticle;
    }

    public List<ShoppingCartLine> getShoppingCart() {
        return shoppingCart;
    }
    
    public int getShoppingCartSize() {
        int fullQuantity = 0;
        for (ShoppingCartLine shoppingCartLine : shoppingCart) {
            fullQuantity += shoppingCartLine.getQuantity();
        }
        return fullQuantity;
    }
    
    public void goPrevious() {
        if ( --currentPosition < 1 ) {
            currentPosition = articleCount;
        }
        currentArticle = ArticleDAO.getArticleById( currentPosition );
    }
    
    public void goNext() {
        if ( ++currentPosition > articleCount ) {
            currentPosition = 1;
        }
        currentArticle = ArticleDAO.getArticleById( currentPosition );
    }
    
    public void addCurrentArticle() {
        for (ShoppingCartLine shoppingCartLine : shoppingCart) {
            if ( shoppingCartLine.getArticle().getIdArticle() == currentArticle.getIdArticle() ) {
                shoppingCartLine.increaseQuantity();
                return;
            }
        }
        shoppingCart.add( new ShoppingCartLine( currentArticle, 1 ) );
    }
    
}
La classe CatalogBrowser

La classe ShoppingCartLine

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
package fr.koor.webstore.business;

public class ShoppingCartLine {

        private Article article;
        private int quantity;
        
        
        public ShoppingCartLine( Article article, int quantity ) {
            setArticle( article );
            setQuantity( quantity );
        }
        
        
        public Article getArticle() {
            return article;
        }
        
        public void setArticle(Article article) {
            if ( article == null ) throw new NullPointerException();
            this.article = article;
        }
        
        public int getQuantity() {
            return quantity;
        }
        
        public void setQuantity(int quantity) {
            this.quantity = quantity;
        }
        
        
        public void increaseQuantity() {
            this.quantity++;
        }
        
    
}
La classe ShoppingCartLine

La classe Login.java (le contrôleur du MVC utilisé pour la page login)

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
 55 
 56 
 57 
 58 
 59 
 60 
 61 
 62 
 63 
 64 
package fr.koor.webstore.ihm;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import fr.koor.webstore.business.CatalogBrowser;
import fr.koor.webstore.business.User;
import fr.koor.webstore.dao.DAOContext;
import fr.koor.webstore.dao.UserDAO;

/**
 * Servlet implementation class Login
 */
@WebServlet(urlPatterns="/login", loadOnStartup=1)
public class Login extends HttpServlet {
    
    private static final long serialVersionUID = -4319076288258537575L;


    @Override
    public void init() throws ServletException {
        DAOContext.init( this.getServletContext() );
    }
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
              throws ServletException, IOException {
        request.setAttribute( "login", "" );
        request.setAttribute( "password", "" );
        request.setAttribute( "errorMessage", "" );
        request.getRequestDispatcher( "/login.jsp" ).forward( request, response );
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) 
              throws ServletException, IOException {
        String login = request.getParameter( "txtLogin" );
        String password = request.getParameter( "txtPassword" );
        
        request.setAttribute( "login", login );
        request.setAttribute( "password", password );
        
        User connectedUser = UserDAO.isValidLogin( login, password );
        if ( connectedUser != null ) {
            
            HttpSession session = request.getSession( true );
            session.setAttribute( "connectedUser", connectedUser );
            session.setAttribute( "catalogBrowser", new CatalogBrowser() );
            request.getRequestDispatcher( "/viewArticle.jsp" ).forward( request, response );
        
        } else {
        
            request.setAttribute( "errorMessage", "Bad identity" );         
            request.getRequestDispatcher( "/login.jsp" ).forward( request, response );
            
        }
        
    }

}
La classe Login.java

La classe ViewArticle.java (le contrôleur du MVC utilisé pour la page viewArticle)

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
 32 
 33 
 34 
 35 
 36 
 37 
 38 
 39 
 40 
 41 
 42 
 43 
 44 
 45 
 46 
 47 
 48 
 49 
 50 
 51 
 52 
 53 
 54 
package fr.koor.webstore.ihm;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import fr.koor.webstore.business.CatalogBrowser;

@WebServlet( "/viewArticle" )
public class ViewArticle extends HttpServlet {

    private static final long serialVersionUID = 550038282401302959L;


    @Override
    protected void doGet( HttpServletRequest request, HttpServletResponse response )
              throws ServletException, IOException {
        HttpSession session = request.getSession( true );
        if ( session.getAttribute( "connectedUser" ) == null ) {
            response.sendRedirect( "login" );
            return;
        }
        
        request.getRequestDispatcher( "/viewArticle.jsp" ).forward( request, response );
    }
    
    @Override
    protected void doPost( HttpServletRequest request, HttpServletResponse response ) 
              throws ServletException, IOException {
        HttpSession session = request.getSession( true );
        if ( session.getAttribute( "connectedUser" ) == null ) {
            response.sendRedirect( "login" );
            return;
        }

        CatalogBrowser browser = (CatalogBrowser) session.getAttribute( "catalogBrowser" );
        
        if ( request.getParameter( "btnPrevious" ) != null ) {
            browser.goPrevious();
        } else if ( request.getParameter( "btnNext" ) != null ) {
            browser.goNext();
        } else if ( request.getParameter( "btnAdd" ) != null ) {
            browser.addCurrentArticle();
        }
        
        request.getRequestDispatcher( "/viewArticle.jsp" ).forward( request, response );
    }
    
}
La classe ViewArticle.java

La vue login

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Login screen</title>
        <link rel="stylesheet" type="text/css" href="styles.css" />
    </head>
    <body>
        <h1>Login screen</h1>
    
        <form method="post" action="login">

            Login: 
            <input name="txtLogin" value="${login}" autofocus />
            <br/>

            Password: 
            <input name="txtPassword" type="password" value="${password}" />
            <br/> <br/>
            
            <input type="submit" value="Connect" />
            <br/><br/>
            
            <div class="errorMessage">${errorMessage}</div>
            
        </form>
    
    </body>
</html>
La vue login

La vue viewArticle

 1 
 2 
 3 
 4 
 5 
 6 
 7 
 8 
 9 
 10 
 11 
 12 
 13 
 14 
 15 
 16 
 17 
 18 
 19 
 20 
 21 
 22 
 23 
 24 
 25 
 26 
 27 
 28 
 29 
 30 
 31 
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>View article</title>
        <link rel="stylesheet" type="text/css" href="styles.css" />
    </head>
    <body>
        <h1>View article - ${connectedUser.login}</h1>
        <br/>
       
        Identifier: ${catalogBrowser.currentArticle.idArticle} <br/>
        Brand: ${catalogBrowser.currentArticle.brand} <br/> 
        Description: ${catalogBrowser.currentArticle.description} <br/>
        Unitary price: ${catalogBrowser.currentArticle.unitaryPrice} <br/>
        <br/>
        
        <form action="viewArticle" method="post">
            <input name="btnPrevious" type="submit" value="Previous" />
            &nbsp; &nbsp;
            <input name="btnAdd" type="submit" value="Add to shopping cart" />
            &nbsp; &nbsp;
            <input name="btnNext" type="submit" value="Next" />
        </form>  <br/>
        
        ${catalogBrowser.shoppingCartSize} article(s) in the shopping cart.<br/>
        <a href="summary">View the shopping cart</a>
    </body>
</html>
La vue viewArticle


Web Filters Contrer les attaques XSS