SQL Database password protected submission

Question

How do I make the following database only submit the entries if the password matches '1996' - I have tried looking into this and can't find out anything. The following could also have a display.php file that has the database details on and they also have the correct pin coding. I just don't know how to make this part of the coding make sure the pin is correct before submitting the details and if the pin is incorrect then an error message apears.

<?php

class simpleCMS {

  var $host;
  var $username;
  var $password;
  var $db;
  var $pin;

    public function display_public() {
    $q = "SELECT * FROM sianDB4 ORDER BY created DESC LIMIT 4";
    $r = mysql_query($q);
    $entry_display = '';

    if ( $r !== false && mysql_num_rows($r) > 0 ) {
      while ( $a = mysql_fetch_assoc($r) ) {

    $title = ($a['title']);
    $bodytext = ($a['bodytext']);
    $author = ($a['author']);

    $entry_display .= <<<ENTRY_DISPLAY
 <div class="post">
<h2>
$title
</h2>
<h3>
$bodytext
</h3>
 <p>
$author
 </p>
</div>
ENTRY_DISPLAY;
      }
        } else {
      $entry_display = <<<ENTRY_DISPLAY

        <h2> This Page Is Under Construction </h2>
        <p>
          No entries have been made on this page. 
          Please check back soon, or click the
          link below to add an entry!
         </p>

     ENTRY_DISPLAY;
         }
        $entry_display .= <<<ADMIN_OPTION

       <p class="admin_link">
          <a href="{$_SERVER['PHP_SELF']}?admin=1">Add a New Entry</a>
          </p>

ADMIN_OPTION;

    return $entry_display;
   }

       public function display_admin() {
         return <<<ADMIN_FORM

      <form action="{$_SERVER['PHP_SELF']}" method="post">

      <label for="title">Title:</label><br />
      <input name="title" id="title" type="text" maxlength="150" />
      <div class="clear"></div>

      <label for="bodytext">Body Text:</label><br />
      <textarea name="bodytext" id="bodytext"></textarea>
      <div class="clear"></div>

      <label for="author">Author:</label><br />
      <textarea name="author" id="author"></textarea>
      <div class="clear"></div>

      <label for="pin">Pin:</label><br />
      <input name="pin" id="pin" type="Password" maxlength="4" />
      <div class="clear"></div>

      <input type="submit" value="Create This Entry!" />
    </form>

ADMIN_FORM;
  } 


   public function write($p) {

       if ( $_POST['title'] )
           $title = mysql_real_escape_string($_POST['title']);
       if ( $_POST['bodytext'])
           $bodytext = mysql_real_escape_string($_POST['bodytext']);
       if ( $_POST['author'])
           $author = mysql_real_escape_string($_POST['author']);
       if ( $title && $bodytext && $author ) {
           $created = time();
          $sql = "INSERT INTO sianDB4                      
VALUES( '$title','$bodytext','$author','$created')";
      return mysql_query($sql);
      }else{
       return false;
     }
 }

  public function connect() {
    mysql_connect($this->host,$this->username,$this->password,$this->pin)    or die("Could not connect. " . mysql_error());
    mysql_select_db($this->db) or die("Could not select database. " . mysql_error());

    return $this->buildDB();
  }

  private function buildDB() {
    $sql = <<<MySQL_QUERY
CREATE TABLE IF NOT EXISTS sianDB4 (
title       VARCHAR(150),
bodytext    TEXT,
author  TEXT,
created     VARCHAR(100)
)
 MySQL_QUERY;

    return mysql_query($sql);
  }

    }

    ?>

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 09 '15 at 13:51

score 0 · Answer 1 · answered Dec 09 '15 at 16:22

As noted by @Jay, the use of the mysql_* suite of functions is not to be recommended anymore so hopefully you can make use of the code below which uses mysqli instead.

I'm not sure how you were using or presenting the class to the user but you'll no doubt be able to make the necessary changes.

<?php
    class simplecms{
        /*
            Pass in the dbconn as a parameter to this class's constructor
        */
        private $db;
        private $pin;


        public function __construct( dbconn $db=null, $pin=false ){
            $this->db=$db;
            $this->pin=intval( $pin );
        }


        public function display_public() {
            $sql='select * from `siandb4` order by `created` desc limit 4';

            $res=$this->db->query( $sql );

            /* use an array rather than concatenating a string for output */
            $html=array();

            if( $res ){
                while( $rs = $res->fetch_object() ){
                    $html[]="
                    <div class='post'>
                        <h2>{$rs->title}</h2>
                        <h3>{$rs->bodytext}</h3>
                        <p>{$rs->author}</p>
                    </div>";
                }
            } else {
                $html[]="
                <h2>This Page Is Under Construction</h2>
                <p>No entries have been made on this page. Please check back soon, or click the link below to add an entry!</p>";
            }

            /* hide this from ordinary users somehow */
            $html[]="
            <p class='admin_link'>
                <a href='{$_SERVER['SCRIPT_NAME']}?admin=1'>Add a New Entry</a>
            </p>";

            /* Add the admin form */
            $html[]=$this->display_admin();

            /* display stuff */
            echo implode( PHP_EOL, $html );
        }



        public function display_admin() {
            $message='';

            if( $_SERVER['REQUEST_METHOD']=='POST' ){/* Add record to the db if the pin matches */
                $message=$this->write() ? 'Database has been updated' : 'Sorry, unable to add that record - check your PIN is correct';
            }

            $admin = isset( $_GET['admin'] ) ? intval( filter_input( INPUT_GET, 'admin', FILTER_SANITIZE_NUMBER_INT ) ) : false;

            return $admin ? "
                <style>
                    form#admin, form#admin *{display:block;box-sizing:content-box!important;}
                    form#admin{ width:50%;display:block;clear:both;float:none;margin:0 auto;}
                    form#admin label{width:100%;clear:both;float:none;margin:0.5rem auto 3rem auto;padding:0.25rem;}
                    form#admin label input, form#admin textarea{float:right;width:60%;padding:1rem;}
                    form#span{color:red;}
                </style>

                <form id='admin' method='post'>
                      <label for='title'>Title:<input name='title' id='title' type='text' maxlength='150' /></label>
                      <label for='bodytext'>Body Text:<textarea name='bodytext' id='bodytext'></textarea></label>
                      <label for='author'>Author:<textarea name='author' id='author'></textarea></label>
                      <label for='pin'>Pin:<input name='pin' id='pin' type='Password' maxlength='4' /></label>
                      <input type='submit' value='Create This Entry!' />
                      <span>{$message}</span>
                </form>" : "";
        }




        public function write(){
            $pin        =   isset( $_POST['pin'] )          ? intval( filter_input( INPUT_POST, 'pin', FILTER_SANITIZE_NUMBER_INT ) )   : false;
            $title      =   isset( $_POST['title'] )        ? filter_input( INPUT_POST, 'title', FILTER_SANITIZE_STRING )               : false;
            $bodytext   =   isset( $_POST['bodytext'] )     ? filter_input( INPUT_POST, 'bodytext', FILTER_SANITIZE_STRING )            : false;
            $author     =   isset( $_POST['author'] )       ? filter_input( INPUT_POST, 'author', FILTER_SANITIZE_STRING )              : false;

            if ( $title && $bodytext && $author && $pin===$this->pin ) {
                /* ? not sure you really want to run this each and every time but... */
                $this->buildtbl();

                /* Prepare the sql and execute - return status */
                $sql='insert into `sianDB4` set `title`=?, `bodytext`=?, `author`=?;';
                $stmt=$this->db->prepare( $sql );
                $stmt->bind_param( 'sss', $title, $bodytext, $author );

                return $stmt->execute();
            }

            return false;
        }


        private function buildtbl(){/* build the table - slightly modified */
            $sql='create table if not exists `siandb4` (
                    `id` int(10) unsigned not null auto_increment,
                    `title` varchar(150) null default null,
                    `bodytext` text null,
                    `author` text null,
                    `created` timestamp null default current_timestamp,
                    primary key (`id`)
                )engine=innodb;';
            $this->db->query( $sql );
        }

    }//end class











    class dbconn{
        /* Simple mysqli db connection */
        private $conn;

        public function __construct( $dbhost, $dbuser, $dbpwd, $dbname ){
            $this->conn=new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
        }
        public function query( $sql ){
            return $this->conn->query( $sql );
        }
        public function prepare( $sql ){
            return $this->conn->prepare( $sql );
        }
    }//end class
?>

<html>
    <head>
        <title>Simple CMS - Hello Kitty Example!</title>
        <style>
            h2,h3{font-size:1rem;}
            div.post{font-size:0.85rem;border-bottom:1px dotted gray;margin:0 auto 3rem auto;}
        </style>
    </head>
    <body>
        <h1>Simple CMS - Hello Kitty Example!</h1>
        <?php

            $dbhost =   'localhost';
            $dbuser =   'root'; 
            $dbpwd  =   'xxxxxx'; 
            $dbname =   'xxxxxx';

            $db=new dbconn( $dbhost, $dbuser, $dbpwd, $dbname );
            $cms=new simplecms( $db, 1996 );

            $cms->display_public();

            $db=$cms=null;

        ?>
    </body>
</html>

SQL Database password protected submission

1 Answers1