cluce Posted June 25, 2007 Share Posted June 25, 2007 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? Quote Link to comment Share on other sites More sharing options...
effigy Posted June 25, 2007 Share Posted June 25, 2007 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? Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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 Quote Link to comment Share on other sites More sharing options...
effigy Posted June 25, 2007 Share Posted June 25, 2007 I thought mysql_real_escape_string was enough, but perhaps I've fell behind the times. Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 25, 2007 Share Posted June 25, 2007 I did a short write-up about dynamic stored procedures in my blog: http://www.treenode.net/ I hope this helps. Thanks! Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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; } } ?> Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 25, 2007 Share Posted June 25, 2007 the error is from not having an actual connection to your mysql database, make sure you're using the write username/password Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 no thats not it because eveerything works fine without it. Quote Link to comment Share on other sites More sharing options...
effigy Posted June 25, 2007 Share Posted June 25, 2007 You're not error checking the connection or queries. Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 25, 2007 Share Posted June 25, 2007 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!"; Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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 Quote Link to comment Share on other sites More sharing options...
effigy Posted June 25, 2007 Share Posted June 25, 2007 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. Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 25, 2007 Share Posted June 25, 2007 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 Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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. Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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 Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 25, 2007 Share Posted June 25, 2007 Repost your code. I'm guessing you're using a mixed method (OOP/Procedural but I don't know. Quote Link to comment Share on other sites More sharing options...
cluce Posted June 25, 2007 Author Share Posted June 25, 2007 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??? Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 26, 2007 Share Posted June 26, 2007 Not really, username/password could include a simple '; <insert malicious code here> Quote Link to comment Share on other sites More sharing options...
cluce Posted June 26, 2007 Author Share Posted June 26, 2007 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); Quote Link to comment Share on other sites More sharing options...
TreeNode Posted June 26, 2007 Share Posted June 26, 2007 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); Quote Link to comment Share on other sites More sharing options...
cluce Posted June 26, 2007 Author Share Posted June 26, 2007 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); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.