I am developing the search engine based on PHP and MySQL with two tables (users, records). I am just a coder of HTML/CSS and beginner in PHP. So I have found some scripts for working PHP-page with database using SQL-queries. At present I have two questions and in the process new questions will be. Any comments and proposals are welcome but please explain each of them. So there is one page on php with forms every of which has action=PHP_SELF. Two forms (searching and login/signup) are visible for all and two others (adding/editing record and edit user data) are visible after the registered user is logged in. How all works: new user signs up to create his own account. Next he logs in and insert into the database some text, tags and selected location from dropdown list. At any moment he can edit this data (his record) and also his user data (ON DUPLICATE KEY UPDATE). My questions are: 1. The query for searching has WHERE `tags` LIKE '%$keywords%' AND `city`="'.$city.'" PHP: and it works ok when all rows in the database with needed keywords have the same cities. But if the cities are different I get the error Both parameters should have an equal number of elements (I use foreach array_combine to display the resluts), how can I fix it? 2. It seems the next questions go together so I decided to post them as one: if I send any form (by GET or POST method) for example if I log in, I get something like /index.php?userlogin=myname&pw=somepassword at the address bar. In my login script I have rows $_SESSION['user']= $_GET['userlogin']; $_SESSION['userid'] = $userid; // from sql-query PHP: as I can understand mean I get the user session. But if I am logged in and do something else for example insert text into the database, I get the url index.php only and I log out though I did not click the button Logout with session_destroy(). How to resolve this, at the same time how to enable https (or other protection, there will be payment process over time), how to encrypt password (there are already mysql_real_escape_string and stripslashes but md5 or sha256 do not work) and how to protect my project in general (including do I must organize my scripts in separate php files and if so why, is it right to move dbconfig up one level and out of the public_html folder using include('../dbconfig.php') and so on)? Any your comments, thanks in advance ps. I know that my code is simple but I have reasons not to post him fully
Your first problem is likely in the REST of your query -- when you SELECT are you including CITY in the result set? Your second problem -- first you should NEVER pass a username/password via GETDATA -- it's vulnerable enough as $_POST without making it insanely easy to hack using $_GET. Generally speaking the only thing you should probably store in the session is the user ID -- pull the rest of the user's data from the database on each pageload. (and that does NOT include their password, leave that out!) You should only be assigning that login on login -- instead of passing it on each and every access. Most likely that's your issue. Set it once at login, and you're logged in, you can stop passing it in the URL or checking it at that point. It also looks like you're plugging values into your queries with string addition old-school, which is an insecure and often slower method of doing things -- you should look into "prepared queries" using PDO or mysqli... if you are using mysql_ functions like mysql_connect, you are learning from decade old sources that you REALLY should ignore at this point, as we've been told for EIGHT YEARS to stop using those. Hence the GIANT RED WARNING BOXES in the documentation. of course that's why most of the idiots writing books and tutorials and the folks sleazing out CMS systems any old way with their heads wedged up 1997's backside are STILL using that outdated halfwit garbage and suggesting others do the same!
@deathshadow: I can see that from a security-standpoint, leaving only the userid in a session might make sense, but doesn't pulling the rest of the info from the DB at every page-load increase traffic a lot? I mean, leaving usernames, emails etc. in a session should be faster, shouldn't it? I'm just curious - it depends, of course, on what you need to access on each page - if you're only checking to see if a user is logged in, you could do that with an active user-session/userid - but what if you need a bit more info?
Session info is usually STORED in a database (typically mysql) though often also as flat text files (grossly inefficient) anyways across each .php file access -- so there's no reason to make an extra database copy of the same information for nothing. Sessions can actually take longer and result in larger tables that are harder to index if you store more information in them. (as it's a three field set with untyped data) Oh, and by "increase traffic" I assume you mean to the SQL engine? (since it would have jack **** to do with HTTP traffic) If it's going to have to go to a SQL database every time you use a session (which PHP does) it's wasteful to make a second copy of the same data -- and it's less overhead since you don't have to copy and write that data to the session database on login. DB programming 101, avoid making extra copies of data when a pointer can do the job -- and that's all a decent auto-increment ID is, a pointer. Which is where JOIN and UNION often create more overhead and more SQL traffic increasing memory overhead, not less! -- no matter what "conventional wisdom" says about decreasing the number of queries.
Oh, another reason to favor it is if you're using file based sessions, that 'file' is 'locked' for the entire duration, so if you have other .php executing session based operations from that same user (like multiple tabs or dynamic requests) those will hang until each one is done -- it's serialized. Database stored sessions might not have that issue, but can have locking contention issues their own. If you are only reading the ID and PHP is using a database for sessions, you get a lot less 'congestion' between requests pulling the rest of the information from your own database setup. It's a reason a lot of developers won't even use $_SESSION and instead use their own tracking cookie and database driven sessions, avoiding the issue entirely!
@deathshadow, thank you for reply! I had resolved the first problem once I switched to the PDO and used foreach($pdo->query($result) as $row). I noticed another problem with the cities. If I select "All cities" from dropdown list I need to get results regardless of the DB column "city" i.e. I need something like $city = $_GET['selectCity']; if($city == 'All+cities') { $city = ' * '; } SELECT ... AND `city`='".$city."' PHP: How should I write it properly? Regarding the second problem. As I wrote I'm PHP beginner (and maybe even less) and I don't know what method is oldest or newest, I had found php and sql ready-made solutions on the web only. Unfortunately I'm not understand how can I pass username/password not by GET/POST method and how to make "assigning that login on login". Can you show me simple examples? Or simple examples for this method. Thanks.
Ok, I have found some codes for authorization and sessions. Now I have: $userlogin = $_POST['userlogin']; $pass = $_POST['pass']; $stmt = $pdo->prepare('SELECT `userid` FROM users WHERE userlogin = :userlogin AND password = :pass LIMIT 1'); $stmt->bindParam(':userlogin', $userlogin); $stmt->bindParam(':pass', $pass); $stmt->execute(); $rows = $stmt->rowCount(); if($rows > 0) { session_start(); new MySqlSession($pdo); $user = $stmt->fetch(); $_SESSION['userid'] = $user['userid']; $_SESSION['time'] = time(); ... PHP: If I echo $_SESSION['userid'] I get proper userid. I included the file sessions.php for class MySqlSession with code by the link avedo.net/402/mysql-based-session-mangagement-in-php (based on session_set_save_handler) and added interface SessionManager mentioned by that link. Also I created the table "sessions" in the database for storing sessions with columns id, value and last_updated. But after authorization any data don't store in this table and if I try to edit and save some user data in the database I get success but I return to main page as like session expires. As I can understand sessions have still not worked. Can anyone help me?
Where are the content you posted above being run? In index.php, or in login.php or something similar? Do you have session_start(); at the top of all files? I'm also a little unsure as to why you create a new $pdo after the if ($rows > 0) check. You could also reduce the lines of code by doing the assignments in the execute-function, like this: $stmt->execute(array(':userlogin'=>$userlogin, ':pass'=>$pass)); PHP: and remove the bindParam-lines. Also, the assignment of $rows is unnecessary, you coudl just do if ($stmt->rowCount() > 0) since you don't seem to be using the $rows-variable anywhere else.
Thanks. I have decided to make the search main page on PHP, but the pages for signing up, logging in and for adding records to DB on the base of Joomla where the user will be redirected to if he clicked the button of account. Now only I need here: what should I do that I get search results regardless of the DB column "city" if I selected "All cities". I wrote if($city == 'All cities') { $resultstmt = $pdo->query("SELECT `text` FROM `records` WHERE `tags` LIKE '%$keywords%'"); } else { $resultstmt = $pdo->query("SELECT `text` FROM `records` WHERE `tags` LIKE '%$keywords%' AND `city`='".$city."'"); } PHP: and it works good but I'm sure it must be some more correct way to do this thing. And most importantly: how to protect my search.php as more as possible, apart from using mysql_real_escape_string & stripslashes? (or they are not from PDO?) and moving dbconfig up one level and out of the public_html folder using include('../dbconfig.php')?