Jump to content

[SOLVED] can I call a stored procedure in php and pass the username and password strings


cluce

Recommended Posts

I am thinking of doing a strored procedure in mysql.........CREATE PROCEDURE login() SELECT username, f_name, l_name FROM employees WHERE username = '$checkuser' AND password = '$checkpassword' LIMIT 1//

 

and call it in my php page........

 

//trims and strips tags

$checkuser = trim(strip_tags($_POST['username']));

$checkpassword = trim(strip_tags($_POST['password']));

CALL login()//

 

can anybody tell me if this will work? If not any recommendations?

Link to comment
Share on other sites

thats what I hear too but I am getting all kinds of errors with that function...............maybe you can help me with that?/

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\wamp\www\userlogin_e.php on line 10

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\wamp\www\userlogin_e.php on line 10

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:\wamp\www\userlogin_e.php on line 11

 

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:\wamp\www\userlogin_e.php on line 11

 

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\userlogin_e.php:10) in C:\wamp\www\userlogin_e.php on line 94

 

here is my code...

<?php
//initialize the session
session_start();

//connect to server and select database
$mysqli = mysqli_connect("localhost", "root", "", "test");


//trims and strips tags
$checkuser = mysql_real_escape_string(trim(strip_tags($_POST['username'])));
$checkpassword = mysql_real_escape_string(trim(strip_tags($_POST['password'])));

//create and issue the query
$sql = "SELECT username, f_name, l_name FROM employees WHERE username = '$checkuser' AND password = '$checkpassword' LIMIT 1";
$result = mysqli_query($mysqli, $sql);

//gets number of unsuccessful logins
$sql1 = ("SELECT failed_logins FROM employees WHERE username = '$checkuser' LIMIT 1");
$result1 = mysqli_query($mysqli, $sql1);
$resultarr = mysqli_fetch_assoc($result1);
$attempts = $resultarr["failed_logins"];

//disables user if failed logins >= 3 
if ($attempts >= 3){

//records unsuccessful logins
$sql1 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql1);

$_SESSION['disabled'] = "<font color='red'>Your account has been disabled.<br>Please contact the MIS department.</font>";
header("Location: employee_resource.php");

//close connection to MySQL
mysqli_close($mysqli);
exit();
} else {

//get the number of rows in the result set; should be 1 if a match
if (mysqli_num_rows($result) == 1) {

//if authorized, get the values of f_name l_name
while ($info = mysqli_fetch_array($result)) {
	$f_name = stripslashes($info['f_name']);
	$l_name = stripslashes($info['l_name']);
}
//set authorization cookie
setcookie("auth", "1", 0, "/", "r.com", 0);
$_SESSION['usersname'] = $f_name . " " . $l_name;

//get last successful login
$last_login = ("SELECT DATE_FORMAT(last_login,  '%b %e %Y at %r') aS last_login FROM employees WHERE username = '$checkuser' LIMIT 1");
$result = mysqli_query($mysqli, $last_login);
$result_login = mysqli_fetch_assoc($result);
$_SESSION['login'] = $result_login["last_login"];

//record last login
    $sql2 = "UPDATE employees SET last_login=NOW() WHERE username = '$checkuser' LIMIT 1";   
    mysqli_query($mysqli,$sql2);

//clears failed logins
$sql3 = "UPDATE employees SET failed_logins = 0 WHERE username = '$checkuser' LIMIT 1";
mysqli_query($mysqli, $sql3);

//sets session to authenticate
$_SESSION['loggedin_e'] = "yes";
  
//sets session to identify
$_SESSION['identity'] = $checkuser;

//close connection to MySQL
mysqli_close($mysqli);

//sets login timer
$current_time = time(); // get the current time
    $_SESSION['loginTime']=$current_time; // login time
    $_SESSION['lastActivity']=$current_time; // last activity

//directs authorized user
header("Location: resource.php");
exit(); 
} else {

//records unsuccessful logins
$sql4 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql4);

//stores a session error message
$_SESSION['error'] =  "<font color='red'>Invalid username and/or password combination<br>Please remember that your password is case sensitive.</font>"; 
	  
  	//close connection to MySQL
mysqli_close($mysqli);

//redirect back to login form if not authorized
header("Location: employee_resource.php");
exit;
}
}
?>

Link to comment
Share on other sites

You're not error checking the connection or queries.

.... which means you don't know if "eveerything works fine" unless you have something like:

$mysqli = mysqli_connect("localhost", "root", "", "test");
if (mysqli_connect_errno())
  echo "Error Connecting to the MySQL database!";

Link to comment
Share on other sites

well I took out the error checking because this website is going to be published soon. I know it works because I am able to login and view the other pages and posts topics to my message board I created.  I even added the error check back in my code and it passes it right up. I was told I need to watch out for double escaping what ever that may be or if I am doing that

Link to comment
Share on other sites

Published websites do not remove all of their error checking(!), they just make sure it's clean and either isolated from the user, or understandable to the user.

I know. since I am new to this it was easier for me to take all the errors out. I dont know how to customize my error checking yet. 

I just realized you're using MySQLi, so, you'll need to use the MySQLi version of real_escape_string:

http://us2.php.net/manual/en/function.mysqli-real-escape-string.php

 

 

u kiddin. I should have saw that I will try it.

Link to comment
Share on other sites

I used the mysqlil_real_escape_string.....now I am down to 2 errors..

 

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in C:\wamp\www\userlogin_e.php on line 11

 

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\userlogin_e.php:11) in C:\wamp\www\userlogin_e.php on line 99

Link to comment
Share on other sites

IM just using procedural.  OOP I dont fully understand yet.  I got it to work like this...

//connect to server and select database
$mysqli = mysqli_connect("localhost", "root", "", "test");

//trims and strips tags
$checkuser = trim(strip_tags($_POST['username']));
$checkpassword = trim(strip_tags($_POST['password']));

//create and issue the query
$sql = "SELECT username, f_name, l_name FROM employees WHERE username = '$checkuser' AND password = '$checkpassword' LIMIT 1";
mysqli_real_escape_string($mysqli, $sql);
$result = mysqli_query($mysqli, $sql);

 

will this tolerate an sql injection???

Link to comment
Share on other sites

thats what I thought.  I think I got it now....This is what I did. this is before all my sql statements. thanks for the feedback

 

//trims and strips tags and escapes fields

$checkuser = trim(strip_tags($_POST['username']));

$checkpassword = trim(strip_tags($_POST['password']));

mysqli_real_escape_string($mysqli,$checkuser);

mysqli_real_escape_string($mysqli,$checkpassword);

 

Link to comment
Share on other sites

Great. Keep in mind that you will not always need to strip_tags, but you should use a conditional statement to check to see if globals are activated or not. Also, mysqli_real_escape_string a return function, so it should be:

$checkpassword = mysqli_real_escape_string($mysqli,$checkpassword);

Link to comment
Share on other sites

Great. Keep in mind that you will not always need to strip_tags, but you should use a conditional statement to check to see if globals are activated or not. Also, mysqli_real_escape_string a return function, so it should be:

by globals, do you mean check for session variables to validate a user on every page view???

 

and the return function would be this........

.. I dont know how to use it? or where to put it?

function real_escape($string) {

      return  get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($string)):mysql_real_escape_string($string);

}

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.