PHP - Database Check (Simple)
Hi, I have a PHP script, that sends post data to the database, and yes I know it is a very simple, and possibly unsecure script, but this is my foundation. Can anyone please tell me a very simple script to add to this, that will check if the username already exists, and if ity does, to send the user to another page?
<?php
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];
mysql_connect(\"localhost\", \"username\", \"password\") or die(mysql_error());
mysql_select_db(\"database\") or die(mysql_error());
mysql_query(\"INSERT INTO `table` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
?>Thanks in Advance
All the best news here: https://newsbotnet.com
kazimmerman posted this at 13:13 — 15th December 2007.
He has: 698 posts
Joined: Jul 2005
Something like this is what I typically would do; you can secure it up or perhaps someone else here can:
<?php$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];
mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
//This is my added script
//Select all rows where the username is the same as the username they entered
$findUserName = mysql_query("SELECT username FROM table WHERE username=$username");
//Count how many results there are
$fUNCount = mysql_num_rows($findUserName);
//If username doesn't exist, add to table and locate 'registered' page
if ($fUNCount == 0) {
mysql_query("INSERT INTO `table` VALUES ('$fname', '$lname', '$email', '$username', '$password')");
header('location: registered.php');
}
//If it does exist, return them to the registration page or an error page
else {
header('location: differentPage.php');
}
?>
That should work for you.
Kurtis
DarkLight posted this at 15:00 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
you are a star!
I cannot seem to grasp php languages, I am fairly new to them.
Thank you so much, I really am grateful for this
kazimmerman posted this at 15:50 — 15th December 2007.
He has: 698 posts
Joined: Jul 2005
Not a problem. Just realize that I have been at this for about 3 years now and I'm still focusing on the basics. It's a process, for sure.
DarkLight posted this at 17:02 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
Hi, i think there is a problem with this code...
<?php
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];
mysql_connect(\"localhost\", \"username\", \"password\") or die(mysql_error());
mysql_select_db(\"database\") or die(mysql_error());
$findUserName = mysql_query(\"SELECT username FROM users WHERE username=$username\");
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else {
header('location: username_taken.php');
}
?>
On line 10 and 13. i have tried editting it, but no decent results.
any ideas will be very much appreciated.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 17:06 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
The only other thing I would do is escape the post fields to prevent SQL injection.
<?php
$fname=mysql_real_escape_string($_POST['fname']);
$lname=mysql_real_escape_string($_POST['lname']);
$email=mysql_real_escape_string($_POST['email']);
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);
mysql_connect(\"localhost\", \"username\", \"password\") or die(mysql_error());
mysql_select_db(\"database\") or die(mysql_error());
//This is my added script
//Select all rows where the username is the same as the username they entered
$findUserName = mysql_query(\"SELECT username FROM table WHERE username=$username\");
//Count how many results there are
$fUNCount = mysql_num_rows($findUserName);
//If username doesn't exist, add to table and locate 'registered' page
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `table` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
//If it does exist, return them to the registration page or an error page
else {
header('location: differentPage.php');
}
?>
DarkLight posted this at 17:20 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
Hi, the code>..
<?php
$fname=mysql_real_escape_string($_POST['fname']);
$lname=mysql_real_escape_string($_POST['lname']);
$email=mysql_real_escape_string($_POST['email']);
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);
mysql_connect(\"localhost\", \"username\", \"password\") or die(mysql_error());
mysql_select_db(\"database\") or die(mysql_error());
//This is my added script
//Select all rows where the username is the same as the username they entered
$findUserName = mysql_query(\"SELECT username FROM table WHERE username=$username\");
//Count how many results there are
$fUNCount = mysql_num_rows($findUserName);
//If username doesn't exist, add to table and locate 'registered' page
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `table` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
//If it does exist, return them to the registration page or an error page
else {
header('location: differentPage.php');
}
?>
returns this>..
But, I know every password is correct, and all tables and databases are correct. alose, I removed all spaces before after and inside the code. but i dont get a working result.
The previous code returned a more understandable error.
Any suggestions?
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 18:37 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
The connection statements need to be before the mysql_real_escape statements.
<?php
mysql_connect(\"localhost\", \"username\", \"password\") or die(mysql_error());
mysql_select_db(\"database\") or die(mysql_error());
$fname=mysql_real_escape_string($_POST['fname']);
$lname=mysql_real_escape_string($_POST['lname']);
$email=mysql_real_escape_string($_POST['email']);
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);
//This is my added script
//Select all rows where the username is the same as the username they entered
$findUserName = mysql_query(\"SELECT username FROM table WHERE username=$username\");
//Count how many results there are
$fUNCount = mysql_num_rows($findUserName);
//If username doesn't exist, add to table and locate 'registered' page
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `table` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
//If it does exist, return them to the registration page or an error page
else {
header('location: differentPage.php');
}
?>
DarkLight posted this at 19:28 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
grr, this is one continuous loop!
This seem to authorise the connection, but now it is saying that
<?php$fUNCount = mysql_num_rows($findUserName);
?>
is not a valid sql results resource, idk whats wrong with it.
Is there something missing from the script?
here is the error statement...
Do you understand this? cuzz I don't.
PS: If I seem a bit ratty, or ungratefull, plz dont think this, I am just a bit fed up of this code, i have been trying to do this for days.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 19:49 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Ok, the mysql_num_rows() is failing because you don't have a query for it to access. In your case, that means that the previous query had an error.
Put this below your first query:
<?phpif (!$findUserName) /* query failed */
echo mysql_error();
?>
pr0gr4mm3r posted this at 19:53 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
There is no identical replacement for that function.
DarkLight posted this at 20:17 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
hi, right... heres how it is...
I tinkered about with it a bit, and finally managed to get rid of the errors with this...
<?php$findUserName = mysql_query(\"SELECT username FROM users WHERE username=$username\");
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
But now all it does is write EVERYTHING into the database, regardless of duplicates.
Can someone please modify this code to make it work. Logically it looks fine, but Technically, It Aint.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 20:24 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Where do you set $fUNCount? Since you aren't setting it to anything, it's always 0, and thus the record will always be inserted, duplicate or not. You need this line: $fUNCount = mysql_num_rows($findUserName);
If that line is causing raising that warning you posted a couple posts back, then that first query is triggering an error. Try this:
<?php$findUserName = mysql_query(\"SELECT username FROM users WHERE username=$username\");
if (!$findUserName)
echo 'MySQL Error: ' . mysql_error();
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
DarkLight posted this at 20:38 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
That code returns the same error as last time, online now it has an added complication
<?php$findUserName = mysql_query(\"SELECT username FROM users WHERE username=$username\");
if (!$findUserName)
echo 'MySQL Error: ' . mysql_error();
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
is what I now have...
And it now returns this...
Why is this simple coding so problamatic??? It should only take a couple of minutes, not 48 hours. This just goes to show, I have lots to learn before being a guru, lol.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 20:45 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Ah ha! There's your problem. You have an error in your SQL query. Look at the first line of that output. That's a SQL error caught and printed by this code:
<?phpif (!$findUserName)
echo 'MySQL Error: ' . mysql_error();
?>
Try the code below. I modified the query a little, and I also assigned the query to a variable before running it. That way, it can output the query if it has an error and show us exactly where the problem is.
<?php$query = \"SELECT 'username' FROM users WHERE 'username' = $username\";
$findUserName = mysql_query($query);
if (!$findUserName)
echo 'Query: ' . $query;
echo 'MySQL Error: ' . mysql_error();
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
DarkLight posted this at 20:51 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
The results is the same, exept it gives a more detailed error statement...
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 21:03 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Ok, I think the problem is fixed. I just put the $username in quotes.
<?php$query = \"SELECT 'username' FROM users WHERE 'username' = '$username'\";
$findUserName = mysql_query($query);
if (!$findUserName)
echo 'Query: ' . $query . '<br />';
echo 'MySQL Error: ' . mysql_error();
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
DarkLight posted this at 21:11 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
Wierd, lol, I had just thought of doing that, then you said it. Yes, this sorts that error, but Now I am stuck with this...
BTW, I have just had a look at your website, Very nice, and very helpful, like the layout. But i noticed you had an IP address above the signatured URL, and that it is broken. just so you know.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 21:22 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
That warning is caused by running a function that affects the header of the page being sent. In other words, functions like header() & setcookie() must be ran before any output is sent to the browser, or you will get that warning. I'm guessing that you have one of those functions online 18 of your code. You will have to move that command before any output to the browser, or enable output buffering.
Thanks for the comment on my website. Is that IP you speak of 127.0.0.1? That's the IP that points to your home machine. Thus, "There is no place like 127.0.0.1." Translates to "There is no place like home."
DarkLight posted this at 21:25 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
no no, I cannot do that, I have all headers set after the tasks...
<?php$query = \"SELECT 'username' FROM users WHERE 'username' = '$username'\";
$findUserName = mysql_query($query);
if (!$findUserName)
echo 'Query: ' . $query . '<br />';
echo 'MySQL Error: ' . mysql_error();
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
?>
If i seem like I havent a clue, then you are probably right, I have oly been using this language for about 3 months.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 21:52 — 15th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Yes, but you can't have it do those functions after any output to the browser. Somewhere, before that header() statement, something is outputting something. Are you outputting any HTML before these statements?
You can take out those three lines that deal with outputting MySQL errors since those are now solved as I'm pretty sure those are the ones causing this.
These are the ones you can remove:
<?phpif (!$findUserName)
echo 'Query: ' . $query . '<br />';
echo 'MySQL Error: ' . mysql_error();
?>
DarkLight posted this at 22:21 — 15th December 2007.
He has: 287 posts
Joined: Oct 2007
Nope, i dont need to, and it still writes everything.
ahh, forget this, I will find another way of doing it, I give up.
Thanks for all your help. I really appreciate it.
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 04:02 — 16th December 2007.
He has: 1,502 posts
Joined: Sep 2006
The satisfaction of finding and solving a problem is greater than abandoning it.
Maybe take a break from it and come back another day, but here is a possible fix.
I actually saw a mistake in the code I provided. Those three lines with that if statement should have had braces. Since they didn't, echo 'MySQL Error: ' . mysql_error(); ran whether or not there was a MySQL error, thus creating an output and messing with your redirects. Here is what it all should be:
<?php
ob_start();
$query = \"SELECT 'username' FROM users WHERE 'username' = '$username'\";
$findUserName = mysql_query($query);
if (!$findUserName)
{
echo 'Query: ' . $query . '<br />';
echo 'MySQL Error: ' . mysql_error();
}
$fUNCount = mysql_num_rows($findUserName);
if ($fUNCount == 0) {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
else{
header('location: username_taken.php');
}
ob_end_flush();
?>
Important: If this isn't the entire page, make sure that ob_start() is the first statement of the script and ob_end_flush() is the last.
DarkLight posted this at 13:42 — 16th December 2007.
He has: 287 posts
Joined: Oct 2007
Very true, I sorted it this morning, I actually started afresh, and coded it myself, and by pure miracle, managed to make it work... for anyone wanting to learn from this thread, here is what I did...
<?php
include(\"dbconnect.php\");
$fname=$_POST['fname'];
$lname=$_POST['lname'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];
mysql_select_db(pcgenius_members)
or die (\"Could not select database because \".mysql_error());
$check = \"select username from users where username = '\".$_POST['username'].\"'\";
$qry = mysql_query($check)
or die (\"Could not match data because \".mysql_error());
$num_rows = mysql_num_rows($qry);
if ($num_rows != 0) {
header('location: username_taken.php');
} else {
mysql_query(\"INSERT INTO `users` VALUES ('$fname', '$lname', '$email', '$username', '$password')\");
header('location: registered.php');
}
?>
It runs like a dream. very simple, yet very effective.
If you find any fatel errors, please let me know immdeiatley, cuzz it is already online, i dont want my database being corrupted, or hacked. Also, you may notice the
<?phpinclude(\"dbconnect.php\");
?>
What do you think? sweet or sinister?
All the best news here: https://newsbotnet.com
DarkLight posted this at 13:48 — 16th December 2007.
He has: 287 posts
Joined: Oct 2007
pr0gr4mm3r: I noticed all your coding works perfect in logic's eyes, but for some reason, my server was not accepting it, do you think it has anything to do with php versions?
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 22:33 — 16th December 2007.
He has: 1,502 posts
Joined: Sep 2006
I never ran that code on my server because I don't have your database or table structure, so it's possible it has problems. That code you just posted looks fine, except you aren't escaping your inputs. I must insist that it is critical for even the smallest application. Not escaping your inputs allows a malicious user to run any query they want on your MySQL server.
DarkLight posted this at 22:51 — 16th December 2007.
He has: 287 posts
Joined: Oct 2007
OK, would that by any chance do this?
replace...
<?php$fname=($_POST['fname']);
?>
with...
<?php$fname=mysql_real_escape_string($_POST['fname']);
?>
Is that what you mean?
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 23:09 — 16th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Yup, that's all that's needed. You might have to connect to the database before you run that though. Just move the mysql_select_db(pcgenius_members) or die ("Could not select database because ".mysql_error()); line to the top.
DarkLight posted this at 23:33 — 16th December 2007.
He has: 287 posts
Joined: Oct 2007
aha, sorted, this is OK. but what exactly would a malicious programmer be able to do with the previous version?
in other words, what harm would queries hold? as long as they arent "INSERT"s
All the best news here: https://newsbotnet.com
DarkLight posted this at 23:35 — 16th December 2007.
He has: 287 posts
Joined: Oct 2007
right, just so we clear, this is my current newsletter process file...
<?php
include(\"dbconnect.php\");
mysql_select_db(pcgenius_members)
or die (\"Could not select database because \".mysql_error());
$category=mysql_real_escape_string($_POST['category']);
$email=mysql_real_escape_string($_POST['email']);
$check = \"select email from newsletter where email = '\".$_POST['email'].\"'\";
$qry = mysql_query($check)
or die (\"Could not match data because \".mysql_error());
$num_rows = mysql_num_rows($qry);
if ($num_rows != 0) {
header('location: email_taken.php');
} else {
mysql_query(\"INSERT INTO `newsletter` VALUES ('$category', '$email')\");
header('location: registered.php');
}
?>
look OK?
All the best news here: https://newsbotnet.com
pr0gr4mm3r posted this at 04:16 — 17th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Yes, that looks good. As for your SQL injection question, here are a couple of resources that could explain it better than I can.
http://en.wikipedia.org/wiki/SQL_injection - Wikipedia article on it
http://unixwiz.net/techtips/sql-injection.html - Several examples
http://www.youtube.com/watch?v=MJNJjh4jORY - Video with the use of SQL injection to break into a university site.
DarkLight posted this at 11:36 — 17th December 2007.
He has: 287 posts
Joined: Oct 2007
interesting... IDK why someone would want to wreck anothers hard work, but thats why we all have firewalls i guess
Thanks Programmer, you have really helped me out these last few days, I am very greatful for everything. and good luck with your own website too.
Thanks again.
All the best news here: https://newsbotnet.com
DarkLight posted this at 11:42 — 17th December 2007.
He has: 287 posts
Joined: Oct 2007
OMFG!!! T^hat is soo scary! will this simple escape string script prevent that? (the video)
can someone please try and do that injection thing on my website, I want to see if it will hold out.
I noticed something... when I upload, then take a look at the online source (via browser) the php script is hidden, how, and why is that? Its good, as it hides the page's tasks and technology, plus if someone copies, and pastes, it wont work properly, but why does it do it? surly there is a more technical reason, not just for security.
All the best news here: https://newsbotnet.com
kazimmerman posted this at 12:46 — 17th December 2007.
He has: 698 posts
Joined: Jul 2005
I can't think of the exact technical way to explain this, but just think of it like this:
PHP is a server-side language that works with the server to render the code. This code is output to the user's computer as HTML, and so there actually is no PHP in the page source. The PHP lies in the file itself, but the viewer only sees the web page.
Does that make any more sense to you than it sounds like it does?
Kurtis
pr0gr4mm3r posted this at 15:35 — 17th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Yup, that function escapes characters like single quotes (') to prevent attacks like this. Just make sure that you escape your inputs and enclose your fields in single quotes (which you are already doing) when you write SQL queries.
You're welcome. Glad to be a help.
DarkLight posted this at 17:07 — 17th December 2007.
He has: 287 posts
Joined: Oct 2007
mscreashuns: I kinda understand, but I will probably be raking wikipedia before the nights out.
Cheers for ur reply.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.