Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: PHP and Oracle

  1. #1
    Join Date
    Apr 2012
    Posts
    19

    Question Unanswered: PHP and Oracle

    I have an Oracle database and I would like to create a php register page for it.
    I can connect to the database but my queries doesn't execute. I would like to have some help with my codes.

    I have attached the sql code.

    This is my PHP :

    <?php
    //Start session
    session_start();

    //Include database connection details
    include 'config.php';

    //Array to store validation errors
    $errmsg_arr = array();

    //Validation error flag
    $errflag = false;

    //Connect to oracle server
    $dbconnect = oci_connect($dbuser, $dbpass, 'dbase1');
    if(!$dbconnect) {
    die("Failed to connect: " . $e['message']);
    }



    //Function to sanitize values received from the form. Prevents SQL injection
    function clean($str) {
    $str = @trim($str);
    if(get_magic_quotes_gpc()) {
    $str = stripslashes($str);
    }
    //return mysql_real_escape_string($str);
    }

    //Sanitize the POST values
    $fname = clean($_POST['fname']);
    $lname = clean($_POST['lname']);
    $login = clean($_POST['login']);
    $password = clean($_POST['password']);
    $cpassword = clean($_POST['cpassword']);

    //Input Validations
    if($fname == '') {
    $errmsg_arr[] = 'First name missing';
    $errflag = true;
    }
    if($lname == '') {
    $errmsg_arr[] = 'Last name missing';
    $errflag = true;
    }

    if($login == '') {
    $errmsg_arr[] = 'Login ID missing';
    $errflag = true;
    }
    if($password == '') {
    $errmsg_arr[] = 'Password missing';
    $errflag = true;
    }
    if($cpassword == '') {
    $errmsg_arr[] = 'Confirm password missing';
    $errflag = true;
    }
    if( strcmp($password, $cpassword) != 0 ) {
    $errmsg_arr[] = 'Passwords do not match';
    $errflag = true;
    }

    //Check for duplicate login ID
    $query="SELECT Username FROM HW4User WHERE Username=:login";

    $stmt=oci_parse($dbconnect,$query);
    if($stmt)//security
    {
    oci_bind_by_name($stmt,':login',$login);
    oci_execute($stmt);
    $num = oci_fetch_all($stmt,$result);
    if( $num!=0)
    {
    $errmsg_arr[] = 'Login ID already in use';
    $errflag = true;
    }
    }

    //If there are input validations, redirect back to the registration form
    if($errflag) {
    $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
    session_write_close();
    header("location: register-form.php");
    exit();
    }
    else {

    //Create INSERT query
    $salt = md5(time());
    $hash = md5($salt . md5($_POST['password']));

    $qry = "INSERT INTO HW4User(Username, RegisterDate, FirstName, LastName) VALUES (:login,:date,:fname,:lname)";
    $qry2 = "INSERT INTO HW4Authentication (UserName, PasswordHash, Salt) VALUES (:login,:hash,:salt)";


    $stmt=oci_parse($dbconnect,$qry);
    $stmt2=oci_parse($dbconnect,$qry2);
    if($stmt)
    {
    oci_bind_by_name($stmt,':login',$login);
    oci_bind_by_name($stmt,':date',$date);
    oci_bind_by_name($stmt,':fname',$fname);
    oci_bind_by_name($stmt,':lname',$lanme);
    oci_bind_by_name($stmt2,':login',$login);
    oci_bind_by_name($stmt2,':hash',$hash);
    oci_bind_by_name($stmt2,':salt',$salt);
    oci_execute($stmt);
    oci_execute($stmt2);
    }
    oci_free_statement($stmt);
    oci_free_statement($stmt2);
    //include("include/_insertWeblog.php?usr_action=\"Account Created\"");
    $act = "Account Created";
    $query = "INSERT INTO HW4Log (UserName,IPAddress,LogDate,action) VALUES (:login,'122324',sysdate,'$act')";
    $stmt = oci_parse($dbconnect,$query);
    if($stmt) {
    header("location: register-success.php");
    exit();
    }else {
    die("Query failed");
    }

    }

    ?>
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    see a picture of my car.
    my car does not go.
    tell me how to make my car go.

    We don't have you tables.
    We don't have your data.
    We don't do PHP.

    No solution is available based upon lack of details provided by you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2012
    Posts
    19
    Quote Originally Posted by anacedent View Post
    see a picture of my car.
    my car does not go.
    tell me how to make my car go.

    We don't have you tables.
    We don't have your data.
    We don't do PHP.

    No solution is available based upon lack of details provided by you.
    Sorry,

    I have attached the sql code.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL appears valid so I suspect PHP code has bugs.
    Code:
      1* SELECT count(*) FROM HW4User
    16:30:35 SQL> /
    
      COUNT(*)
    ----------
    	 0
    
    16:30:36 SQL> INSERT INTO HW4Authentication (UserName, PasswordHash, Salt) VALUES ('JOHN',1,1);       
    
    1 row created.
    
    16:31:39 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2012
    Posts
    19

    Question

    Quote Originally Posted by anacedent View Post
    SQL appears valid so I suspect PHP code has bugs.
    Code:
      1* SELECT count(*) FROM HW4User
    16:30:35 SQL> /
    
      COUNT(*)
    ----------
    	 0
    
    16:30:36 SQL> INSERT INTO HW4Authentication (UserName, PasswordHash, Salt) VALUES ('JOHN',1,1);       
    
    1 row created.
    
    16:31:39 SQL>
    thanks.

    I have successfully debugged the PHP.
    Now I need to create a stored procedure for the table HW4LOG that automatically stores the Username,The IP address, the log date and the kind of action the user is performing on the database like ( account created, login,logout)
    this is what I have come up with and it is not working :

    CREATE OR REPLACE PROCEDURE HW4AddLogEntry (
    p_Username IN VARCHAR2,
    p_IP IN VARCHAR2,
    p_Action IN VARCHAR2,
    p_Date IN DATE
    )
    AS
    BEGIN
    INSERT INTO HW4Log (UserName, IPAddress, LogDate, Action) VALUES (p_Username, p_IP, p_Date,p_Action );
    COMMIT;
    END;
    /

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >this is what I have come up with and it is not working :
    see a picture of my car.
    It is not working.
    How to make my car go?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Apr 2012
    Posts
    19

    Question

    Quote Originally Posted by anacedent View Post
    >this is what I have come up with and it is not working :
    see a picture of my car.
    It is not working.
    How to make my car go?
    I need to create a stored procedure for the table HW4LOG that automatically stores the Username,The IP address, the log date and the kind of action the user is performing on the database like ( account created, login,logout)
    this is what I have come up with and it is not working :

    CREATE OR REPLACE PROCEDURE HW4AddLogEntry (
    p_Username IN VARCHAR2,
    p_IP IN VARCHAR2,
    p_Action IN VARCHAR2,
    p_Date IN DATE
    )
    AS pragma autonomous transaction
    BEGIN
    INSERT INTO HW4Log (UserName, IPAddress, LogDate, Action) VALUES (p_Username, p_IP, p_Date,p_Action );
    COMMIT;
    END;
    /

    this is the create tables statements :


    DROP TABLE HW4Log;
    DROP TABLE HW4Authentication;
    DROP TABLE HW4User;

    CREATE TABLE HW4User (
    UserName VARCHAR2(30) NOT NULL,
    RegisterDate DATE NOT NULL,
    FirstName VARCHAR2(30) NOT NULL,
    LastName VARCHAR2(30) NOT NULL,
    CONSTRAINT PK_User_UserName PRIMARY KEY (UserName)
    );

    CREATE TABLE HW4Authentication (
    UserName VARCHAR2(30) NOT NULL,
    PasswordHash CHAR(40) NOT NULL,
    Salt CHAR(40) NOT NULL,
    CONSTRAINT PK_Auth_UserName PRIMARY KEY (UserName),
    CONSTRAINT FK_Auth_UserName FOREIGN KEY (UserName) REFERENCES HW4User(UserName)
    );

    CREATE TABLE HW4Log (
    UserName VARCHAR2(30) NOT NULL,
    IPAddress VARCHAR2(15) NOT NULL,
    LogDate VARCHAR2(30) NOT NULL,
    Action VARCHAR2(50) NOT NULL,
    LogID INTEGER NOT NULL INITIALLY DEFERRED DEFERRABLE,
    CONSTRAINT PK_Log_UserName PRIMARY KEY(UserName, LogID),
    CONSTRAINT FK_Log_UserName FOREIGN KEY (UserName) REFERENCES HW4User(UserName)
    );

    --Relative sequence HW4Log
    CREATE OR REPLACE TRIGGER HW4log_increment
    BEFORE INSERT ON HW4Log
    FOR EACH ROW
    BEGIN
    SELECT NVL(MAX(LogID), 0)+1 INTO :new.LogID FROM HW4Log;
    END;
    /
    Last edited by louloubrice64; 04-28-12 at 01:52.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >this is what I have come up with and it is not working :
    I am not behind you.
    while "not working" may be 100% valid, it is 100% devoid of actionable detail.
    My car is not working.
    Tell me how to make my car go.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Apr 2012
    Posts
    19

    Question

    Quote Originally Posted by anacedent View Post
    >this is what I have come up with and it is not working :
    I am not behind you.
    while "not working" may be 100% valid, it is 100% devoid of actionable detail.
    My car is not working.
    Tell me how to make my car go.
    I want to create a procedure that Will automatically insert the correct parameters in the HW4log table.

    Look at the HW4log table to see the parameters I want to capture.

    For exemple When a user logs in I want to automatically capture his username,his IP address and the date for that log.
    Last edited by louloubrice64; 04-28-12 at 02:22.

  10. #10
    Join Date
    Apr 2012
    Posts
    19

    Question

    Quote Originally Posted by louloubrice64 View Post
    I want to create a procedure that Will automatically insert the correct parameters in the HW4log table.

    Look at the HW4log table to see the parameters I want to capture.

    For exemple When a user logs in I want to automatically capture his username,his IP address and the date for that log.
    How to track logon & logout ?
    in other words :
    How can I automatically populate the HW4log Table with the user Log history ?
    Last edited by louloubrice64; 04-28-12 at 10:04.

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When PHP is utilized, application is essentially 3-tier as described below.

    EndUser<=>browser<=>WebServer<=>ApplicationServer< =>DatabaseServer

    The only entity above that knows about end users IP# is the WEBSERVER.
    So Webserver must obtain the EndUser IP#, pass it to the ApplicationServer, which passes it explicitly to the DatabaseServer and same for timestamp when connection is initiated.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Apr 2012
    Posts
    19

    Question

    Quote Originally Posted by anacedent View Post
    When PHP is utilized, application is essentially 3-tier as described below.

    EndUser<=>browser<=>WebServer<=>ApplicationServer< =>DatabaseServer

    The only entity above that knows about end users IP# is the WEBSERVER.
    So Webserver must obtain the EndUser IP#, pass it to the ApplicationServer, which passes it explicitly to the DatabaseServer and same for timestamp when connection is initiated.
    Thanks,

    How can I include the end users IP# from the WEBSERVER into this code :

    CREATE OR REPLACE PROCEDURE HW4AddLogEntry
    after logon on database
    declare
    p_Username VARCHAR2,
    p_IP VARCHAR2,
    p_Action VARCHAR2,
    p_Date DATE

    CURSOR c1 IS
    SELECT UserName from HW4user WHERE UserName =userenv( 'login' ));

    BEGIN

    OPEN c1;
    FETCH c1 INTO p_Username;
    INSERT INTO HW4Log (UserName, IPAddress, LogDate, Action) VALUES (p_Username, p_IP, sysdate,p_Action );
    CLOSE c1;
    COMMIT;
    END;
    /

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I include the end users IP# from the WEBSERVER into this code :
    First PHP must obtain client IP# from WebServer.
    Next PHP must pass client IP# to DB which can be done via DBMS_APPLICATION_INFO

    DBMS_APPLICATION_INFO
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Apr 2012
    Posts
    19

    Thumbs up

    Quote Originally Posted by anacedent View Post
    >How can I include the end users IP# from the WEBSERVER into this code :
    First PHP must obtain client IP# from WebServer.
    Next PHP must pass client IP# to DB which can be done via DBMS_APPLICATION_INFO

    DBMS_APPLICATION_INFO
    WOW !!

    How can you know all this stuff about Oracle ?

    You have a PHD in Databases ?

    Thanks for your help

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can you know all this stuff about Oracle ?
    I've been doing Oracle for more than a decade & I remember stuff.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •