Slide 11.10: Server-side JDBC scripts

Server-side JDBC Scripts

This application uses a database table, users, hosted by the server undcemcs01.und.edu. The table is accessed from the following JDBC program, using the GET and POST methods, respectively:

01CREATE TABLE  users (
02   name      VARCHAR(32) PRIMARY KEY,
03   password  VARCHAR(16),
04   role      VARCHAR(16) );
05 
06INSERT INTO  users  VALUES (
07   'Poke Mon', 'hush', 'admin' );
08 
09INSERT INTO  users  VALUES (
10   'Mario', 'secret', 'user' );
11 
12INSERT INTO  users  VALUES (
13   'Super Man', 'knock-knock', 'hacker' );

LoginGet.cgi or LoginPost.cgi for the POST method,
LoginGet.pl or LoginPost.pl for the POST method, and
Login.java for both the GET and POST methods.

http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/520/11/LoginGet.cgi

1#!/usr/bin/bash
2 
3CLASSPATH=.:/usr/lib/oracle/23/client64
4CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ojdbc8.jar
5CLASSPATH=$CLASSPATH:/usr/lib/oracle/23/client64/lib/ottclasses.zip
6export  CLASSPATH
7 
8/usr/bin/perl  LoginGet.pl
9# (for POST) /usr/bin/perl  LoginPost.pl

http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/520/11/LoginGet.pl

01#!/usr/bin/perl
02use CGI;
03$query    = new CGI;
04$username = $query->url_param( 'name' );
05# (for POST) $username = $query->param( 'name' );
06$password = $query->url_param( 'pword' );
07# (for POST) $password = $query->param( 'pword' );
08 
09print ( "Content-type: text/html\n\n" );
10# Remove leading and trailing spacing.
11$username =~ s/^\s*(\S*)\s*$/$1/;
12# For security, remove some Unix metacharacters.
13$password =~ s/;|>|>>|<|\*|\?|\&|\|//g;
14 
15# Compose a Java command.
16$cmd  = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom ";
17$cmd .= "Login '$username' '$password'";
18system( $cmd );

http://undcemcs01.und.edu/~wen.chen.hu/cgi-bin/520/11/Login.java

01/*******************************************************************
02 
03  This Java program is used by both GET and POST methods, whose
04    differences will be shown by LoginGet.pl and LoginPost.pl.
05 
06  To use this program, you need to create the following
07    table by using the commands below:
08 
09  SQL> CREATE TABLE  users (
10    2    username  VARCHAR(32) PRIMARY KEY,
11    3    password  VARCHAR(16),
12    4    role      VARCHAR(16) );
13 
14  SQL> INSERT INTO  users  VALUES ( 'Poke Mon', 'hush', 'admin' );
15 
16  SQL> INSERT INTO  users  VALUES ( 'Mario', 'secret', 'user' );
17 
18  SQL> INSERT INTO  users  VALUES ( 'Super Man', 'knock-knock', 'hacker' );
19 
20*******************************************************************/
21 
22// Import the following packages to use JDBC.
23import  java.sql.*;
24import  java.io.*;
25import  oracle.jdbc.*;
26import  oracle.jdbc.pool.OracleDataSource;
27 
28class  Login {
29  public static void  main( String args[ ] ) throws SQLException {
30    String user     = "C##user_id";
31    String password = "password";
32    String database = "20.185.147.112:1521/xe";
33 
34    // Open an OracleDataSource and get a connection.
35    OracleDataSource ods = new OracleDataSource( );
36    ods.setURL     ( "jdbc:oracle:thin:@" + database );
37    ods.setUser    ( user );
38    ods.setPassword( password );
39    Connection conn = ods.getConnection( );
40 
41    try {
42      // Create, compose, and execute a statement.
43      Statement stmt = conn.createStatement( );
44      String  query  = "SELECT role FROM users WHERE name='";
45              query += args[0].trim( ) + "' AND password='" + args[1].trim( ) + "'";
46      ResultSet rset = stmt.executeQuery( query );
47 
48      // The user name is unique, so at most one row is returned.
49      if ( rset.next( ) )  System.out.print( rset.getString( 1 ) );
50 
51      // Close the ResultSet and Statement.
52      rset.close( );
53      stmt.close( );
54    }
55    catch ( SQLException ex ) {
56      System.out.println( ex );
57    }
58    // Close the Connection.
59    conn.close( );
60  }
61}