...

View Full Version : Date



robojob
09-14-2005, 09:17 PM
What i am trying to do is on my members register page i am attempting to insert the current date and then the current date plus 1 year. The first date is used for signup and the second is expiry of account as accounts will only be valid for one year.

Can anyone help me with this?


Also, does anyone know of a way i can expire accounts in the mysql database when the expiry date is reached?

Brandoe85
09-14-2005, 09:35 PM
You can use mysql's Now() function to insert the current date. And looking through the Date and Time functions (http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html) page, Maybe you can use the Date_Add() function and add 1 year to now().


DATE_ADD(NOW(), INTERVAL 1 YEAR);


Good luck

Kid Charming
09-14-2005, 09:45 PM
Yep, that's how it works. This thread (http://www.codingforums.com/showthread.php?t=67509) may help, too.

robojob
09-14-2005, 09:46 PM
where exactly in the code of my page would i put that? the field in the database is called signup. and do i need to have any specific settings in the database? im a recent newbie to php and mysql but getting to grips with it now!!

Brandoe85
09-14-2005, 10:13 PM
In your insert statement. You have two date fields, signup, and then experation? Well, whatever the names are, when you run your insert query, for the values of signup and experation, use NOW(), and then DATE_ADD(NOW(), INTERVAL 1 YEAR);

If you run into problems post up the code so we can take a look.

Good luck

robojob
09-14-2005, 10:54 PM
k, i tried what you said.

When i enter both sections of code i get an error saying, couldnt execute query, so i tried just adding the current date with this code in my query:


$query = "INSERT INTO users (username,password,email,age,location,sex,msn,website,link,about,address,live,venue,mobile,dob,signu p,expire)
VALUES ('$_POST[username]','$_POST[password]','$_POST[email]','$_POST[age]','$_POST[location]','$_POST[sex]','$_POST[msn]','$_POST[website]','$_POST[link]','$_POST[about]','$_POST[address]','$_POST[live]','$_POST[venue]','$_POST[mobile]','$_POST[dob]','$_POST[signup]','NOW()')";

this posted the signup successfully however the date in the database has appeared as 0000-00-00..... any suggestions? can someone please enter the code into the query for me so i know how to do it...

Thanks for your help guys!

Kid Charming
09-14-2005, 11:01 PM
Don't put quotes around function calls -- single quotes are for strings, so you're literally entering NOW() into your database, which is being converted to 0's, because it's not a valid date. Did you check out the link I posted above? It's got examples of what you're trying to do.

As an aside, you really shouldn't drop variables directly from POST into your query. It opens you to an Injection attack (http://us2.php.net/manual/en/security.database.sql-injection.php).

robojob
09-14-2005, 11:10 PM
hi, that works a dream! gotta figure out now how i can expire passwords after the year subscription, or get users to recieve an email and re-register so thier membership is valid for another year! sure i can work it out!!

Can you just browse this code and tell me if its open to sql injection.... i think its protected but not sure...


<?php

include 'config.php';

function is_alphachar($text) {

for ($i = 0; $i < strlen($text); $i++) {

if (!ereg("[A-Za-z0-9]", $text[$i])) {
return 1;
}
}
}

$form .= "<center><font size=\"2\" face=\"verdana\"><b>Fill out the form below to become a member of the Submerse Members area and get instant access to Submerse exclusives!. <br></b><br></center></font>";
$form .= "<form action=\"./register.php\" method=\"POST\">";
$form .= "============================================";
$form .= "<b><br><font size=\"2\" face=\"verdana\">Please supply the following information for your login details.<br></b></font><br>";
$form .= "<font size=\"1\" face=\"verdana\">Username: <br><input type=\"text\" name=\"username\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Your email: (This will be used to recover your account.)<br><input type=\"text\" name=\"email\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Password: <br> <input type=\"password\" name=\"password\"><br></font>";
$form .= "============================================";
$form .= "<font size=\"2\" face=\"verdana\"><br><b>Please supply the following information for your profile.</b><br><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Age: <br><input type=\"text\" name=\"age\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Date of Birth(ddmmyy): (This will not be publicly displayed)<br><input type=\"text\" name=\"dob\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Location: <br><input type=\"text\" name=\"location\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Sex: <br><select name=\"sex\"><option value=\"male\">male</option><option value=\"female\">female</option></select><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">MSN: <br><input type=\"text\" name=\"msn\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Website: <br><input type=\"text\" name=\"website\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Favourite Link: <br><input type=\"text\" name=\"link\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">About Yourself: <br><textarea name=\"about\"></textarea><br></font>";
$form .= "============================================";
$form .= "<font size=\"2\" face=\"verdana\"><br><b>Please supply the following information for our records.</b><br><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">What is your postal address? <br> (used to send out stickers, newsletters etc) <br><textarea name=\"address\"></textarea><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">What is your mobile number? <br> (needed for us to contact you regarding backstage access) <br><input type=\"test\" name=\"mobile\"><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">Ever seen Submerse Live?: <br><select name=\"live\"><option value=\"yes\">Yes</option><option value=\"no\">No</option></select><br></font>";
$form .= "<font size=\"1\" face=\"verdana\">If yes, Where? <br><input type=\"text\" name=\"venue\"><br></font>";
$form .= "<input type=\"submit\" value=\"Create!\">";
$form .= "</form>";

if($_POST[username] == ""){
echo $form;
} elseif(strlen($_POST[password]) < 6){
echo $form;
echo "<br> Error password must be 6 characters or more";
} else {
$connection = mysql_connect($hostname, $user, $pass)
or die(mysql_error());
$db = mysql_select_db($database, $connection)
or die(mysql_error());


$sql = "SELECT username FROM users
WHERE username = '$_POST[username]'";

$sql2 = "SELECT email FROM users
WHERE email = '$_POST[email]'";

$result = mysql_query($sql)
or die ("Couldn't execute query.");

$result2 = mysql_query($sql2)
or die ("Couldn't execute query.");

$num = mysql_num_rows($result);
$num2 = mysql_num_rows($result2);

if (is_alphachar($_POST[username]) == 1) {
echo $form;
echo "Invalid Username. Only numbers/letters and underscores are allowed.<br>";
die;
}
if ($num == 1) {


echo "Error, username already exists!";

} elseif ($num2 == 1) {
echo "Error, that email address has already been registered. Please select a different one.";
} else {

$query = "INSERT INTO users (username,password,email,age,location,sex,msn,website,link,about,address,live,venue,mobile,dob,signu p,expire)
VALUES ('$_POST[username]','$_POST[password]','$_POST[email]','$_POST[age]','$_POST[location]','$_POST[sex]','$_POST[msn]','$_POST[website]','$_POST[link]','$_POST[about]','$_POST[address]','$_POST[live]','$_POST[venue]','$_POST[mobile]','$_POST[dob]',NOW(),DATE_ADD(NOW(), INTERVAL 1 YEAR))";
$resultB = mysql_query($query,$connection) or die ("Coundn't execute query.");
echo "Congratulations! Your account has been created!";
echo "<br><a href=\"index.php\">Back to login area</a>";
}
}
?>

raf
09-15-2005, 12:52 AM
Can you just browse this code and tell me if its open to sql injection....

yes it is. You should run the
if (is_alphachar($_POST[username]) == 1) {
check before your select.

i could insert a new users in your userstable or possibly even create a new mysql account (depending on the permissions your account has).

you should check all userinput (username, password, email etc) before using it inside a query, not after using it.

by the way: there isn't even a single line in your code (except the php-tags) that can't be improved. (i know; i'm not known as a nice guy). if you're intrested in getting your code straightened out, then post back and we can write you a 'best practice' version of it.

robojob
09-15-2005, 08:20 PM
k, if you can write a best practice version. i would be interested to see it.

raf
09-15-2005, 09:30 PM
Something like this:


<?php
require('config.php'); // require will give a fatal error if the fale can not be included. include will just give a warning and then continue. since you're grabbing your mysql accountdetails here, a warning isn't enough.
// we don't need that functio you wrote. there is a build in function that does exactly the same : ctype_alnum()

$post_uname = trim($_POST['username']);
$post_pwd = trim($_POST['password']);
$post_email = trim($_POST['email']);
// and so on for all formfields
// you should also check all other formfields here using regex and ctype-function
$validinput = True;
if($post_uname == ""){
$validinput = False;
$errormessage = '<br />No username supplied.';
}
if (!ctype_alnum($post_uname)){
$validinput = False;
$errormessage .= '<br />Invalid username. Use only numbers and letters.';
}
if(strlen($post_pwd) < 6){
$validinput = False;
$errormessage .= '<br /> Error password must be 6 characters or more';
}
if(!ctype_alnum($post_pwd)){
$validinput = False;
$errormessage .= '<br />Invalid password. Use only numbers and letters.';
}
if(!eregi('^[_\.0-9a-zA-Z-]+@([0-9a-zA-Z]+\.)+[a-zA-Z]{2,6}$', $post_email)) {
$validinput = False;
$errormessage .= '<br />Invalid email adress.';
}
// and so on for all formfields
if (!$validinput){
// your concatination for the $form variable is inefficient + your string can better be inclosed in single quotes
//i din't change the html, but it's clear that you should make it valid xhtml + do your layouting through css

echo '<center><font size="2" face="verdana"><b>Fill out the form below to become a member of the Submerse Members area and get instant access to Submerse exclusives!. <br></b><br></center></font>
<form action="./register.php" method="POST">
============================================
<b><br><font size="2" face="verdana">Please supply the following information for your login details.<br></b></font><br>
<font size="1" face="verdana">Username: <br><input type="text" name="username"><br></font>
<font size="1" face="verdana">Your email: (This will be used to recover your account.)<br><input type="text" name="email"><br></font>
<font size="1" face="verdana">Password: <br> <input type="password" name="password"><br></font>
============================================
<font size="2" face="verdana"><br><b>Please supply the following information for your profile.</b><br><br></font>
<font size="1" face="verdana">Age: <br><input type="text" name="age"><br></font>
<font size="1" face="verdana">Date of Birth(ddmmyy): (This will not be publicly displayed)<br><input type="text" name="dob"><br></font>
<font size="1" face="verdana">Location: <br><input type="text" name="location"><br></font>
<font size="1" face="verdana">Sex: <br><select name="sex"><option value="male">male</option><option value="female">female</option></select><br></font>
<font size="1" face="verdana">MSN: <br><input type="text" name="msn"><br></font>
<font size="1" face="verdana">Website: <br><input type="text" name="website"><br></font>
<font size="1" face="verdana">Favourite Link: <br><input type="text" name="link"><br></font>
<font size="1" face="verdana">About Yourself: <br><textarea name="about"></textarea><br></font>
============================================
<font size="2" face="verdana"><br><b>Please supply the following information for our records.</b><br><br></font>
<font size="1" face="verdana">What is your postal address? <br> (used to send out stickers, newsletters etc) <br><textarea name="address"></textarea><br></font>
<font size="1" face="verdana">What is your mobile number? <br> (needed for us to contact you regarding backstage access) <br><input type="test" name="mobile"><br></font>
<font size="1" face="verdana">Ever seen Submerse Live?: <br><select name="live"><option value="yes">Yes</option><option value="no">No</option></select><br></font>
<font size="1" face="verdana">If yes, Where? <br><input type="text" name="venue"><br></font>
<input type="submit" value="Create!">
</form>', $errormessage;
}else{
/*
All this should be in a seperate file that you include (with require() ) and that is placed above the webroot
$connection = mysql_connect($hostname, $user, $pass)
or die(mysql_error());
$db = mysql_select_db($database, $connection)
or die(mysql_error());
*/

$sql = "SELECT count(*) FROM users WHERE username = '". $post_uname ."'";
$result = mysql_query($sql, $connection) or die("Couldn't execute query username.");
if (mysql_result($result,0) >= 1){
echo "Error, username already exists!";
} else {
$sql = "SELECT count(*) FROM users WHERE email = '". $post_email ."'";
$result = mysql_query($sql, $connection) or die("Couldn't execute query email.");
if (mysql_result($result,0) >= 1){
echo "Error, email already exists!";
}else{
$query = "INSERT INTO users (username,password,email,age,location,sex,msn,website,link,about,address,live,venue,mobile,dob,signu p,expire)
VALUES ('". $post_uname ."','". $post_uname ."','". $post_email ."','$_POST[age]','$_POST[location]','$_POST[sex]','$_POST[msn]','$_POST[website]','$_POST[link]','$_POST[about]','$_POST[address]','$_POST[live]','$_POST[venue]','$_POST[mobile]','$_POST[dob]',NOW(),DATE_ADD(NOW(), INTERVAL 1 YEAR))";
// all these $_POST[age] fields should also be made safe with mysql_real_escape_string(), after you checked that their valueformat was correct and they didn't contain unexpected/illegal values
$resultB = mysql_query($query,$connection) or die ("Coundn't execute query.");
if (mysql_affected_rows($resultB) === 1){
echo 'Congratulations! Your account has been created!
<br /><a href="index.php">Back to login area</a>';
}else{
echo 'Error! Your account has not created';
}
}
}
}
?>


Of course, whan you wanna do serious PHP work, then you'll probably have a formprocessing classe to ease checking the posted values.

I just worked out the first three as an example, but you of course also need to check and process all other posted fields

When i check a posted form, i always check all fields and don't stop after the first error. Also, inside my formprocessing code, i set the class of the formfields that were invalid. because i use css for my layout, i can then very easy change the backgroundcolour of all invalid fields to make it easier on the user to correct them.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum