Arduino Keypad working via Ethernet with Apache Server

Hi to all,
here is the code from my first project with Arduino: A Keypad working with Arduino over ethernet to a apache server which has a MYSQL Database to chek if the persion is allowed to enter a door. The password is not crypted, which I will change, later.

// Include libraries
#include <SPI.h>
#include <Ethernet.h>
#include <Keypad.h>
#include <EEPROM.h>

byte mac[] = { 0x90, 0xA2, 0xDA, 0x00, 0x34, 0x99 }; //Arduino MAC
byte ip[] = { 172,20,12,28 };        // Arduino IP
byte router[] = { 172,20,12,253 };   // Network Router IP 
byte server[] = { 172,20,12,145 };    // Server IP
Client client(server, 80);           // Serverinformation for client, were to send data


 //---------------------------------------

const byte ROWS = 4; // Four rows
const byte COLS = 3; // Three columns
int  i = 0;          // Counter for password
char passwort[] = {'0','0','0','0','0','0'}; // Password array of 6 signs

 //----------------------------------------------------------
 
char keys[ROWS][COLS] = // Keymap define

{
	{'1','2','3'},
	{'4','5','6'},
	{'7','8','9'},
	{'*','0','%'}  // # can't be used, it is a used variable of apache Server, replaced by %
};

byte rowPins[ROWS] = {3, 8, 7, 5}; // Connect to the row pinouts of the keypad
byte colPins[COLS] = {4, 2, 6};    // Connect to the column pinouts of the keypad

Keypad keypad = Keypad( makeKeymap(keys), rowPins, colPins, ROWS, COLS );  // Define keypad variables from keypad.h and

 //----------------------------------------------------------

void setup() //Single Task
{
  
  pinMode(12, OUTPUT);  // Define Pin 12 as OUTPUT 13= not so strong for output current(used by system as check led)
  Serial.begin(9600);   // Boudrate at 9600 bits/s 
  
  //for (int i = 0; i < 512; i++) // Write a 0 to all 512 bytes of the EEPROM
  //EEPROM.write(i, 0);  
  //Serial.println("EEPROM empty" );  
}

 //----------------------------------------------------------
 
 
 void loop() //Repeat Task

{
         char pass = '1'; // While true check for key pressed
         while ( 1 )
        
          {
        
             pass = keypad.getKey(); // Read in key's
        
             if (pass != NO_KEY) break; // Break to stop loop
                
          }
          
            Serial.println(pass); // Print key
          
            passwort[i] = pass; // Write single key in char array passwort
            i++;
         
            if ( i == 6 ) // When password array of 6 charecters are full, go to functions
           { 
              sendpass(passwort);    // Take variable password to function sendpass
              char c = getdata();    // Start funktion getdata with c as get variable
              ckeckdata(c);          // Check server information about password 
              i = 0;                 // Reset password to zero
           }
}                         
                                   
 //----------------------------------------------------------
 

 void sendpass(char* passwortsend)    // Send password to Server
 
 {
       String p;                      // Counting password array together to be ready fpr TCP/IP
       for ( int j=0; j<6; j++)       // Convert password array to string password
       {
       p += passwortsend[j];
       }
       for (int i = 0; i < 512; i++) // Write a 0 to all 512 bytes of the EEPROM
       EEPROM.write(i, 0);  
       Serial.println("EEPROM empty" ); 
       Serial.println(p);
       Serial.println("ethernet...");
       Ethernet.begin(mac, ip, router); // Start the Ethernet connection MAC,IP,Router
       Serial.begin(9600);              // Start the serial library
       delay(3000);                     // Give the Ethernet shield 3 second to initialize
       Serial.println("connecting...");

 
  if (client.connect())          // If you get a connection, report back via serial
  { 
        String s = ("GET /cgi-bin/post.pl?I=2&W="); // Send password to URL with GET order /cgi-bin/=Apache Server   post.pl=perl script for database communication   ?=start parameter  &=link data
        s += p;                                     // Combine s=URL and p=password
        Serial.println("connected"); 
        Serial.println(s); // Show password string with URL
        client.println(s); // GET password send to server  
}
  
  else {
   
        Serial.println("connection failed");  // Didn't get a connection to the server
  }
  
 }
 
 //----------------------------------------------------------

 char getdata()        //Get Y or N from Server
 
 { 
 char data;
      
    while (1)
  {
        if (client.available()) 
  {
        Serial.println("Data Read"); // Waiting for answer from Apache
        data = client.read();
        Serial.print(data);
    break;
  }

  }                                        
      if (!client.connected())     // If server's disconnected, stop the client
  {
         Serial.println(data);
         Serial.println("disconnecting.");
         
    client.stop();   
  }
  return data;
}
 
 //----------------------------------------------------------
 
 void ckeckdata(char data)

 {                                     
       if (data == 'Y') // Y Password check  
      
       {   
          for (int i = 0; i < 512; i++) // Write 0 to all 512 bytes of the EEPROM
           EEPROM.write(i, 0);  
           Serial.println("EEPROM empty" );
           
           Serial.println("Accepted");        
           digitalWrite(12, HIGH);
           delay(1000);
           digitalWrite(12, LOW);
           delay(10);         
       }
       
       else            // Y Password check
       
       {
          Serial.println("Denied");  
          digitalWrite(12, HIGH);
          delay(100);
          digitalWrite(12, LOW);
          delay(10);
          
          for (int i = 0; i < 512; i++)    // Write 0 to all 512 bytes of the EEPROM
          EEPROM.write(i, 0);  
          Serial.println("EEPROM empty" );
       }        
 }

The password is not crypted, which I will change, later.

While you are at it, you could use one of the non-numeric keys to indicate end-of-password, so that the password is not required to be exactly 6 characters long.

Nice job, though.

I'm curious about the server script being written in perl. Why not PHP which is optimized to work with MySQL databases?

Can you post the perl script, too?

Hi PaulS,
here is the perl script and the PHP script.
Sorry for no comments but our Network Department wrote it, not me who works at the Technic Department.
Both Versions are working fine, if you need any vocabulary just use google translator German-English.

Post.pl script

#!/usr/bin/perl
# MJ: /Applications/MAMP/cgi-bin
# MJ: sh-3.2# perl post.pl W=*1234%23 I=1
# MJ: http://172.30.11.241/cgi-bin/post.pl?W=*1234%23&I=1
# http://localhost/cgi-bin/post.pl?I=1&W=*1234%

use CGI::Carp qw( fatalsToBrowser );

# PERL MODULES WE WILL BE USING

use Data::Dumper;
use CGI qw/:standard/;

use DBI;
use DBD::mysql;

my $firstvalue = param('W')    or die "Value1 not in parameter list";
my $lastvalue = param('I')    or die "Value2 not in parameter list";

# CONFIG VARIABLES
$platform = "mysql";
$database = "Zutrittssystem";
#$host = "172.20.12.145";
$host = "localhost";
$port = "3306";
$tablename = "Schliessort";
$user = "root";
$pw = "root";

# DATA SOURCE NAME
$dsn = "dbi:$platform:$database:$host:$port";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw) or die "Unable to connect: $DBI::errstr\n";

$vtime = (time()*1000)-1000;

# PREPARE THE QUERY
#$query = "INSERT INTO Buchung (ID, text) VALUES ($lastvalue,$vtime)";
#$query = "INSERT INTO Buchung (ID, text) VALUES ($currentvalue,$lastvalue)";

#$query_handle = $connect->prepare($query);


# EXECUTE THE QUERY
#$query_handle->execute();

$vtime = time()*1000;

$open = false;
$IDGet = "9";
$KennungGet = "999999";
sleep 1;
  
$query = "SELECT * FROM Schliessort ORDER BY ID";
$query_handle = $connect->prepare($query);
$query_handle->execute();
$query_handle->bind_columns(\$IDGet, \$KennungGet);
while($query_handle->fetch())
  {
    if ($IDGet eq $lastvalue and $KennungGet eq $firstvalue)
      {
        $open = true;
      }
  }
  

$timestamp = time();
  
#if ($firstvalue eq '*1234%')
if ($open eq true)
{
  $query = "INSERT INTO Buchung (ID, Zeitstempel, OK) VALUES ($lastvalue,$timestamp,$open)";
  $query_handle = $connect->prepare($query);
  $query_handle->execute();

  # HTTP HEADER
  print "Content-type: text/html \n\n";
  print "Yes, you are allowed to enter the TechnikCenter - COME IN -\n\n";
  #print "Y";
  #printf $timestamp;
  print header;
}

else
{
  $query = "INSERT INTO Buchung (ID, Zeitstempel, OK) VALUES ($lastvalue,$timestamp,$open)";
  $query_handle = $connect->prepare($query);
  $query_handle->execute();

  # HTTP HEADER
  print "Content-type: text/html \n\n";
  print "No, you are not allowed to enter the TechnikCenter - GO AWAY -\n\n";
  #print "N";
  #printf $timestamp;
  print header;
}

PHP script

                 $firstvalue =  htmlspecialchars($_GET["I"]) or die('Fehler. Es wurde kein Standort übergeben.');
                $lastvalue = htmlspecialchars($_GET["W"]) or die('Fehler. Es wurde keine gültige Benutzerkennung eingegeben.');

                echo 'Standort: '. $firstvalue ."
". ' Benutzerkennung: '.$lastvalue."
";

// CONFIG VARIABLES
/*Die Verwendung von globalen Variablen ermöglicht uns eine sehr dynamische Anwendung, wir brauchen nur den Wert hier ändern
*und schon funktioniert das Programm immernoch. Wenn wir das nicht machen würden, müssten wir bei jeder Änderung
*den gesamten Quellcode durchsuchen und die Definitionen austauschen. Macht wenig Sinn, da wir sowieso wenig Geld von der Bank kriegen. ;)
*/

$database = 'Zutrittssystem';
$host = 'localhost';
$port = '3306';
$tablename = 'Schliessort';
$tablename2 = 'Buchung';
$user = 'root';
$pw = 'root';


// DATA SOURCE NAME definieren, ist definiert durch die Deklaration der Variable $host und $database
echo 'DatabaseSourceName (DSN/dsn) : '. $host."
";



//Verbinden des Codes bzw den Anweisungen mit der Datenbank
/*mysql_connect() erwartet den Pfad (meistens localhost), Username und Passwort
 * sollte die Übergabe bzw. die Verbindung fehlschlagen gibt es einen Fehler der dann in der HTML Datei ausgegeben wird
 */

$connect = mysql_connect($host, $user, $pw) or die('Es konnte keine Verbindung aufgebaut werden, Fehler:'.  mysql_error());
mysql_select_db($database);

//Das Programm pausiert 1s
sleep(1);

//Wir senden hier einen SQL Befehl an die Datenbank und Sortieren diese aufsteigend nach der ID, schlägt dies Fehl wird ein
//Fehler ausgegeben.

$link = mysql_query('SELECT * FROM '.$tablename.' ORDER BY ID');
if (!$link) {
   die('Ungültige Abfrage: '.mysql_error()."
");
}


//Die Zählvariable i wird mit 0 initialisiert
$i = 0;

/*
$meta = mysql_fetch_field($link, $i);
if (!$meta) {
    echo "No information available
\n";
    }*/

$i = 0;
$open = FALSE;

while ($i < mysql_num_fields($link)){
$suche = mysql_data_seek($link, $i);
$row = mysql_fetch_object($link);

//Daten werden nach dem Modell ausgegeben:
/*Auslesen der Daten
*Standort ID: 1
*Kennung: *1234%
*/
//echo "
<b>".'Auslesen der Daten'."</b>
";
//echo ' Standort ID: '.$row->ID ."
".' Kennung: '.$row->Kennung ."
";
    if ($row->ID == $firstvalue && $row->Kennung == $lastvalue)
      { 
        $open = TRUE;
      }
$i++;
}


$timestamp = time();
if ($open == TRUE){
    $connect2 = mysql_connect($host, $user, $pw);
    mysql_select_db($database);

    //echo mysql_error();
    $query = mysql_db_query($database, 'INSERT INTO '.$tablename2.' (BuchungsID, ID, Zeitstempel, OK) VALUES (0, '.$firstvalue.', '.$timestamp.', '.$open.')');
    if (!$query) {
   die('Ungültige Abfrage: '.mysql_error()."
");
    }
    echo "
".'Yes, you are allowed to enter the TechnikCenter - COME IN, get to work…'."
".$timestamp.'s since 01-01-1970.'.$query;
    }

else {
    echo 'INSERT INTO Buchung (BuchungsID, ID, Zeitstempel, OK) VALUES 0 '.$lastvalue.' '.$timestamp.' '.'0';
    $query = mysql_query('INSERT INTO Buchung (BuchungsID, ID, Zeitstempel, OK) VALUES 0 '.$lastvalue.' '.$timestamp.' '.'0');
    echo "
".'STOP, you arent allowed to enter the TechnikCenter - GO AWAY, probably it is the wrong company?'."
".$timestamp;
    mysql_close();
}

OK I will start this week the preparation for Arduino Wireless Acces Door with LCD Display and RFID Chip instead of a keypad, all with crypted network transfer.
Hope it works out the way I wan't it to be.