homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Inserting Selected Date into MySQL
Rubin_Remus




msg:4057738
 12:25 pm on Jan 10, 2010 (gmt 0)

Hello everyone,

I've been puzzling over the following problem for a little while now, and have scoured the web looking for the solution, but nothing seems to work.

I am sure that the answer is simple enough but need a little assistance.

I have an event registration form, and the user is meant to be able to add all the details to the database. This part actually works fine, but the date of the event is selected from a dropdown menu, which is seperated into "day", "month" and "year".

I was hoping I would be able to convert this to a date for MySQL either before it is inserted into the database, or have it inserted as well as the other three fields.

I'm not familiar with CONCAT or many of SQL's functions and commands, but I have got the impression that CONCAT is the function I should use.

This is my .PHP code inserting the details into the database, so perhaps you'd be able to guide me further. I didn't think posting the form would help very much though.

Thanks for any help you can give, in advance!


$sql="INSERT INTO giglist (venue, town, postcode, day, month, year, time, age, noflier, flier, flierul, notes)
VALUES
('{$_POST['venue']}', '{$_POST['town']}', '{$_POST['postcode']}', '{$_POST['day']}', '{$_POST['month']}', '{$_POST['year']}', '{$_POST['time']}', '{$_POST['age']}', '{$_POST['noflier']}', '{$_POST['flier']}', '{$_FILES['image']['name']}', '{$_POST['notes']}') ";

[edited by: whoisgregg at 12:50 am (utc) on Jan. 13, 2010]
[edit reason] Fixed sidescroll. :) [/edit]

 

rocknbil




msg:4058039
 2:06 am on Jan 11, 2010 (gmt 0)

Welcome aboard Rubin_Remus, I've got some good news and some very very bad news.

First the good: these three fields, unless you have a specific use for them, are a bit of a waste.

day, month, year,

Sure, you could make it work, but let's create a single field for those in standard mySQL date format. This will make your DB faster/leaner, and you can do anything you'd want on this field that you'd want to do with the three.

alter table giglist add event_date date not null default '0000-00-00';

(In reality, being an event, this should really be datetime.)

So now what you do is,


if (($_POST['day'] > 0) and ($_POST['month'] > 0) and ($_POST['year'] > 0)) {
$date = "$_POST['year']-$_POST['month']-$_POST['day']";
}
else { die("Invalid date values supplied."); }
$select = "insert into giglist (event_date) values ('$date');
.....

(Other fields eliminated for clarity)

Now for the bad news. Note in the above I do a test of > 0 for each of these date values. It may seem trivial, but on a very basic level this is a security precaution. Google for mysql injection and XSS or Cross Site Scripting - uncleansed values sent to your database is (IMO) the leading cause of "my site was hacked, what do I do?"

In my example, any attempts at injection,

month='%20and%201=1'
month='<script . . . . '

Will evaluate to zero. And fail.

At the very least - and by itself is not complete - you should apply mysql_real_escape_string to these input values. Entering raw input data to your database is nothing short of suicide.

The first reaction to this type of info is "my users's won't be doing this" or "it's an admin area" but it's not your users you have to worry about . . .

Rubin_Remus




msg:4058184
 8:14 am on Jan 11, 2010 (gmt 0)

Thank you very much for your reply, and whilst I'm a little annoyed regarding the bad news, I appreciate being told before it's too late!

To be honest, I don't think anybody will want to damage this website, but that kind of ignorance never helps, so I will heed your advice.

I don't like to rely on people in forums to help me all the time, as there's so much to learn, so I'll look into database security elsewhere rather than ask you to do it for me.

The only thing I am confused about is exactly where to put the code you've just suggested in relation to the rest of the sql commands.

Also, if I were to use DATETIME instead, would I need to alter much to make it a more effective method of storing the data?

rocknbil




msg:4058667
 10:23 pm on Jan 11, 2010 (gmt 0)

whilst I'm a little annoyed regarding the bad news,

You think that's annoying, wait until invisible divs with malicious Javascript appear on your pages. This search will share some of the pain with you: site hacked site:webmasterworld.com [google.com]

I don't think anybody will want to damage this website

In a perfect world, why would they . . . but like it or not, because they can is a good enough reason, and remains as so.

I'll look into database security elsewhere

Just to clarify, the insecurity is not in the database, it's in how you handle input from forms, so it's more in PHP. Taking databases out of the equation, any form that accepts user input, such as a mailer, is vulnerable to attack.

The only thing I am confused about is exactly where to put the code you've just suggested in relation to the rest of the sql commands.

Depends on how your script is written, but in that example, just before you compose your select statement will work.

Also, if I were to use DATETIME instead, would I need to alter much to make it a more effective method of storing the data?

Not sure what you mean by effective, but informative and usable, yes. And I kinda spoke out of turn on datetime, that's not **really** what you need.

Most management of "events" have a begin and end date for multiple day events, defaulting to the begin date for a single day event, but a start an end time is a handy, if not required, tool.

So you're left with a conundrum based on your requirements. Most of the time it works out like this:

event_start_date (date)
event_sd_start_time (time) ("sd"=start date, see below)
event_sd_end_time (time)
event_end_date (date)
event_ed_end_time (time)

For single date events, you'd like use only event_start_date, event_sd_start_time, event_sd_end_time.

Jan 12 2PM - 4PM

For multiple day events, you will likely not need an end date start time, and may not need the event_sd_end_time either:

starts: Jan 12 2PM
ends Jan 22 4PM

So it's really going to depend on the needs of the project, but overall, I'd say the date and times fields should be separate.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved