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

[cluce]

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?

[effigy]

Conceptually, yes; as you have it written, no. You probably want a function instead of a procedure. Are you expecting it to return some kind of value?

[cluce]

nope no value.  I have it working on my php page but I hear this is best defense against an sql injection. so I am doing some modifactions

[effigy]

I thought mysql_real_escape_string was enough, but perhaps I've fell behind the times.

[TreeNode]

I did a short write-up about dynamic stored procedures in my blog:

http://www.treenode.net/

 

I hope this helps. Thanks!

[cluce]

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;
}
}
?>

[TreeNode]

the error is from not having an actual connection to your mysql database, make sure you're using the write username/password

[cluce]

no thats not it because eveerything works fine without it.

[effigy]

You're not error checking the connection or queries.

[TreeNode]

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!";

[cluce]

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

[effigy]

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.

[TreeNode]

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

 

 

[cluce]

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.

[cluce]

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

[TreeNode]

Repost your code. I'm guessing you're using a mixed method (OOP/Procedural but I don't know.

[cluce]

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???

[TreeNode]

Not really, username/password could include a simple

'; <insert malicious code here>

[cluce]

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);

 

[TreeNode]

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);

[cluce]

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);

}