Date: 11/02/05 (PHP Community)    Keywords: mysql, database, sql, security, spam

for the last few days someone has been testing my various forms to see if they can send 'extra' email headers. stupid spammers...

This got me thinking about ways of 'cleaning up' user input, and I was wondering how other people go about it. I know input validation depends on what exactly the input is (ie guestbook comment would be treated differently to a query string), but in general terms

my current method involves stripping newline characters (\r\n), stripslashes (and then mysql_real_escape_string anything that's going into a database), trim, and strip_tags if need be.
I also have some things that use regular expressions to check/remove any unwanted characters etc. I'm probably missing lots of important things, but I seem to be picking stuff up as I go along, and I'm getting quite paranoid about security, which can't be a bad thing. heh.

Anyone else got a different approach, or any general tips/links?

Source: http://www.livejournal.com/community/php/363396.html

Date: 11/02/05 (PHP Community)    Keywords: php, mysql, software, html, technology, database, sql, security, apache

Edit: Thank you all for your help and suggestions. I had a meeting with my server administrators and have found that it would just be easier to dump and import the data into my own little MySQL database. But I learned a lot in this process thanks to you all!

This is a follow up to the post I made before regarding this database connection problem I'm having. (http://www.livejournal.com/community/php/359304.html)

After receiving responses from people that suggested I install the MS SQL PHP functions, I inquired with my IT contact (we are a large institution and therefore have ridiculous amounts of red tape to deal with) as to whether or not they could be installed.

This is what I was told:

We do not support MSSQL odbc connection on our Sun Solaris server running Apache. mssql_connect would require a staff member who knows the technology, purchase of licensed software (which we do not have), and recompiling of Apache that may potentially break other users code. Also, there would be MSSQL security issues if we were to open up or authorize connections to it from our Sun Server, we host a variety of departmental databases on that SQL server, that could be a potentially serious problem if any of our other databases we to be compromised. Sorry for the inconvenience, but that is the story.


If I can't connect to that database, the entire project that I'm working on is going to be fucked and I have a 11/21 deadline. I need to know two things, if any of you can discern from all of this:

1. Is it EVER going to be possible for me to connect to that database? Or should I just stop wasting my time and create another for my personal use?

2. If it is possible, can someone tell me how? :(

Thanks everyone.

Cross posted to PHP/MySQL communities.

Source: http://www.livejournal.com/community/php/363248.html

Date: 11/03/05 (PHP Community)    Keywords: php, mysql, sql

Hello! I am making an online MMORPG using PHP and MySQL. I have two questions.

First off, how would I make a turn system? As in, for example, I can have up to 100 turns at a time. When I use a turn, stuff happens; I get one turn every 10 seconds or so.

For example...
I have 100 turns.
I use 5. (The stuff the turns do happens.)
I now have 95 turns.
20 seconds pass.
I now have 97 turns.
40 seconds pass.
I now have 100 turns. (Not 101, because 100 is max.)

How could I do something like that using PHP and MySQL?

Secondly, is there a way using PHP (or MySQL) to automatically empty out a certain table at a certain time, even if a page isn't accessed? Like an auto-refresh if you will. I'm thinking that would be on the server, not in a particular script however...because scripts are only run when they're opened.

*Shrugs*

Insight would be helpful. Thank you!

Source: http://www.livejournal.com/community/php/363690.html

Date: 11/04/05 (PHP Community)    Keywords: php, mysql, database, sql

Hi, I'm completely stumped over this. I'm probably missing something quite obvious, and I figure it's to do with using global variables.



There is a file, PostDisplay.php. It loops through an array of posts from the database and prints them out. It is independent of the posts themselves and is called by scripts that specify the posts to be printed. Before the loop are the queries to put the posts into an array and count the number of rows (used in the loop).

$array_post = mysql_query($query_post);
$num_post = mysql_num_rows($array_post);

There are two files so far to specify what posts to print. This one, LatestPost.php, works perfectly.


$latest_post = "SELECT * FROM posts ORDER BY id DESC LIMIT 1";
$query_post = $latest_post;

include('PostDisplay.php');

?>

This one, MonthsPost.php, doesn't.


$year = $_GET['year']; # sets the year of the desired posts as a global variable
$month = $_GET['month']; # sets the month of the desired posts as a global variable

$month_post = "SELECT * FROM posts WHERE month=$month && year=$year";
$query_post = $month_post;

include('PostDisplay.php');

?>

The global variables $year and $month are set in the script months.inc.php. Excerpt:

November

The error given upon loading MonthsPost.php is:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in c:\phpdev5\www\new\PostDisplay.php on line 4
(Line 4 is "$num_post = mysql_num_rows($array_post);".)


If anyone is brave/bored enough to read all that and help, thank you. ^_^

Edit: Using mysql_select_db in PostDisplay.php has fixed it, and so I've done something silly with the includes elsewhere. Thanks for all the help ^_^

Source: http://www.livejournal.com/community/php/364431.html

Date: 11/04/05 (Asp Dot Net)    Keywords: asp, sql, web

ORDER BY CASE WHEN @OrderBy = '1' THEN NEWID()
WHEN @OrderBy = '2' THEN (tblaccommodation.name)
WHEN @OrderBy = '3' THEN (tblaccommodation.FromDT)
END

This is the order by conditional statement i am using
to order an asp.net result set but it keeps returning
this error below for the name feild (text) and sql
server throws up a unique identifier error for FromDT
(a datetime) which asks for the convert fuction to be
used but each of these three works without the Case
statement without any trouble.

The asp.net error for name feild
Syntax error converting from a character string to uniqueidentifier.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting from a character string to uniqueidentifier.

Source Error:

Line 220: ' Create and Fill the DataSet
Line 221: Dim myDataSet As New DataSet()
Line 222: myCommand.Fill(myDataSet)
Line 223:
Line 224: ' Return the DataSet


Source File: c:\inetpub\wwwroot\devotion2motion.com\CodeBehind\AccomSearch.vb Line: 222

Source: http://www.livejournal.com/community/aspdotnet/46138.html

Date: 11/04/05 (Web Development)    Keywords: asp, sql, web

ORDER BY CASE WHEN @OrderBy = '1' THEN NEWID()
WHEN @OrderBy = '2' THEN (tblaccommodation.name)
WHEN @OrderBy = '3' THEN (tblaccommodation.FromDT)
END

This is the order by conditional statement i am using
to order an asp.net result set but it keeps returning
this error below for the name feild (text) and sql
server throws up a unique identifier error for FromDT
(a datetime) which asks for the convert fuction to be
used but each of these three works without the Case
statement without any trouble.

The asp.net error for name feild
Syntax error converting from a character string to uniqueidentifier.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting from a character string to uniqueidentifier.

Source Error:

Line 220: ' Create and Fill the DataSet
Line 221: Dim myDataSet As New DataSet()
Line 222: myCommand.Fill(myDataSet)
Line 223:
Line 224: ' Return the DataSet

Source: http://www.livejournal.com/community/webdev/265072.html

Date: 11/05/05 (MySQL Communtiy)    Keywords: mysql, sql

I have a few tables, one is a collection of test_plans, the other a collection of test_procedures, and a third that joins them. Given the third contains foreign keys to link back to the primary key of a single test plan, and an collection of procedures, how do I select from mysql to determine which procedures have *not* been linked currently to a plan already in the join table?


TIA!

Updated - problem solved!

Source: http://www.livejournal.com/community/mysql/72572.html

Date: 11/05/05 (PHP Community)    Keywords: mysql, sql

I've always had trouble with mysql queries and using variables in them (such as, SELECT * FROM table WHERE id=$x). My current weirdness involves several queries--I have the main query. If certain pieces of information pulled from that query are not null, then it goes into a subquery and processes it. The code looks like this:

while ($a_row = mysql_fetch_array($result)) {
print "< img src=".$a_row['url']." border=0>< br />";
print "< font size=\"-1\">".$a_row['blurb']."< br />";
print "This photo was taken on .".$a_row['dateofpic'];
if ($a_row['event'] != '') {
$a_row['event'] = $picevent;
$subquery1 = 'SELECT * FROM events WHERE id=$picevent';
$subresult1 = mysql_query($subquery1) or die('Query failed: please try again ' . mysql_error());
while ($suba_row = mysql_fetch_array($subresult1)) {
print " during ".$suba_row['eventname'];
}
}

I've bolded the query that's the problem. I've tried it where:

$subquery 1 = 'SELECT * FROM events WHERE id=$a_row['event']';

but that doesn't work--nothing shows up then. With the current query I have, it at least executes the above previous stuff before giving me an error. Any thoughts?

Source: http://www.livejournal.com/community/php/364998.html

Date: 11/11/05 (PHP Community)    Keywords: php, mysql, sql

Does PHP think numbers stored in MySQL tables with ZEROFILL are octal numbers?

For example, suppose I have a column INT(4) ZEROFILL and I insert the number 346 using PHP. MySQL turns that into 0346 - which is octal notation in PHP. Is it now going to treat 0346 as an octal number in math calculations?

Source: http://www.livejournal.com/community/php/366491.html

Date: 11/11/05 (PHP Community)    Keywords: php, mysql, sql

I am using this code to calculate hours:

$hoursITEM = (strtotime($end_time) - strtotime($start_time))/3600;

We want to use "24:00" midnight (military time)at the end of a shift. Unfortunately this results in an error.

Whats the best way to handle this?

***
Whatever I decide to do it's going to effect this MySQL query
$Query = "SELECT invoice_number, sum(((TIME_TO_SEC(end_time) - TIME_TO_SEC(start_time))/3600) * rate) AS subtotal FROM invoice_items WHERE office_id='$office_id' AND service_type!='225' GROUP BY invoice_number ORDER BY invoice_number";


Thanks for your help...

[More thoughts]
I guess we could use 00:00 and then absolute value to get rid of the negative... As long as I can do that with the Query too.


Shoot - 00:00 is interpreted as 0... 00:00 - 06:00 = -6. For the PHP I can juggle it to work. "if "24:00" yada yada


[After doing some test with MySQL]
I guess I dont have to worry about MySQL, its computing:

sum( ( TIME_TO_SEC(24:00) - TIME_TO_SEC(06:00) )/3600 )

as 18 hours.

I guess I can accomodate for the PHP because that part is for display on one page.

After we can wipe out a series of bugs we are going to go back and add an end date. We just need to get this functional for the time being.

Source: http://www.livejournal.com/community/php/366221.html

Date: 01/11/05 (Data Management)    Keywords: database, sql, postgresql

Pervasive looks to crack the database market with a support offering around open-source database PostgreSQL.

Source: http://news.zdnet.com/2100-9592_22-5519933.html

Date: 11/12/05 (PHP Community)    Keywords: php, mysql, database, sql, web

Hello All. Just a short introduction.

I'm Sam, 17 years of age from England. I'm currently a student, and am soon to leave for university to study computer gsmes development, which should be fun.I've been teaching myself php along with MySQL for about a week now, and all has been pretty good so far. It's surprising how simple php really is.

Now thats over with, I have a small problem. I'm not sure if this is the right place to ask, but here goes:

Does anybody have any experience making websites hosted by the free lycos tripod service? If so, how do you get php to communicate with MySQL in it? I've created a site that works fine on the test server I've created, but as soon as I upload it to tripod, it just stops working. The lycos help page said it would automatically connect to and select your database at the beginning of the script, so I figured you just skip all the mysql_connect() stuff and start straight away with the queries, however it still doesnt work. I know for a fact my query is fine, as I copied and pasted it straight into the mysql client and it all worked beautifully.

If all else fails, could you recommend a good, preferably free php and MySQL webhost?

Thankyou Greatly

Sam

Source: http://www.livejournal.com/community/php/366601.html

Date: 11/16/05 (MySQL Communtiy)    Keywords: php, mysql, sql

Hi, I feel a bit silly asking this. I don't think I quite understand how to apply functions such as mysql_num_rows() or MySQL commands such as COUNT().

Obviously I'm having trouble with some code, but I want more general answers before working on the code.

Can anyone explain how to count the number of rows from a table which have a particular value in a particular column? What if one or both of those are variables?

Thanks in advance. :)

Cross-posted to php.

Source: http://www.livejournal.com/community/mysql/72773.html

Date: 11/16/05 (PHP Community)    Keywords: mysql, sql

Hi, I feel a bit silly asking this. I don't think I quite understand how to apply functions such as mysql_num_rows() or MySQL commands such as COUNT().

Obviously I'm having trouble with some code, but I want more general answers before working on the code.

Can anyone explain how to count the number of rows from a table which have a particular value in a particular column? What if one or both of those are variables?

Thanks in advance. :)

Cross-posted to mysql.

Source: http://www.livejournal.com/community/php/367931.html

Date: 11/16/05 (PHP Community)    Keywords: php, mysql, sql

The query I have is this:

$query1 = "SELECT DAYOFMONTH(eventdate) as day, DAYOFMONTH(enddate) as endday, event FROM calendar WHERE MONTH(eventdate) = '$month' AND YEAR(eventdate) = '$year' AND type = 1 ORDER BY day";
$result1 = mysql_query($query1) or die('Query failed: please try again ' . mysql_error());

The accompanying result php code I have is this:

while ($a_row = mysql_fetch_array($result1)) {
if ($a_row['enddate'] != '') {
print "< font size=-2>".$a_row['day']." to ".$a_row['endday']." - ".$a_row['event']."< br>< /font>";
}
else {
print "< font size=-2>".$a_row['day']." - ".$a_row['event']."< br>< /font>";
}
}

I get no errors anywhere; it simply only prints the else part and doesn't examine the if part. Is this just the wrong way to go about it? Or am I missing something elementary?

Source: http://www.livejournal.com/community/php/367619.html

Date: 11/17/05 (PHP Community)    Keywords: database, sql, web, linux

Second post today, I knew there was something else. I just wondered how does everyone go about managing there projects? I've been given the task of redoing my companies database delivery system (wow such an impressive title) basically converting all the Access databases we have into webbased sql databases and front ends, but this project is going to be huge (with a team of around 3 of us, for a 3000user base, yes, this isn't going to work) and I thought my current system of a folder and dropping scripts into just wont cut it, esp for revision control so is it possible or does anyone use cvs or svn for revision control or something simular. I would like something easy but at the end of the day whatever works. I could use a linux box but it's all windows servers so something that can work on windows would be better, which is a shame.
Any ideas is greatly appreciated.

Source: http://www.livejournal.com/community/php/368626.html

Date: 11/17/05 (PHP Community)    Keywords: php, mysql, database, sql

Hello everyone
This is my first post here so please go easy on me. I've been using/developing php for little over a year now, simple things (help desk, asset database) for my company. But there is 1 thing that has always bugged me and I can't find out how to do it.

I've noticed on some site's (blogs and php.net) that they dont have the usually page url, such as
http://www.somedomain.com/pages/about.php
but more like
http://www.somedomain.com/about/
or blog entries like
http://www.somedomain.com/blog/2005/11/09/

I just wondered how do they do that? I don't think they create a new directory for each entry or am I wrong there?

I know that is kinda of a vague question, it's just im redoing my blog atm (i use mysql for storing all my entries) and at the moment the url is usually like http://www.somedomain.com/blog/id=23232 < entry number
I'd really like to change that to http://www.somedomain.com/blog/2005/11/09.

If anyone can help that would be great

Source: http://www.livejournal.com/community/php/368298.html

Date: 11/17/05 (Asp Dot Net)    Keywords: sql

I'm writing a program in C# that is supposed to take a datagridview control that is binded to a SQL query. I'm trying to write something that will loop through each cell and will make the cell invisible if there's no data in it. I'm not sure if I'm supposed to loop it using a column count or through cells or what. I'm pretty new to this control (Its a 2.0 control in case no one knows what this is referring to.) If anyone has any suggestions it'd be greatly appreciated.

Source: http://www.livejournal.com/community/aspdotnet/48214.html

Date: 11/18/05 (C Sharp)    Keywords: database, asp, sql, web

I am converting a web app from ASP to ASP.net and have stumbled on an annoying problem.

1. ASP code accepts user input and stores value in the database
SQL field type is nvarchar(200).
BUT the query does not use N'' to specify that the string is unicode.

2. Value stored in the database is '陳小春'

3. Value displayed on web page (utf8 encoding, no server side changes) is '陳小春'

4. Actual UTF data in the database is displayed on screen as ???
(database value: 人, display value: ?)

5. ASP.net application using the EXACT same queries results in point 4 being displayed correctly (as japanese) but the values that were displayed correctly in the ASP app are now exactly as they are in the database.

Please Help ... My only guess was that the ADO connection was converting the UTF in the database to Non-UTF for display in ASP and the ADO connection in .Net is preserving the Unicodeness of it.

But i did a simple test in ASP where i selected the data, stored it in a variable and then inserted back into the database (non using N'')
and well ... i got the exact same .... japanese was japanese the rest was garbage.


Anyone have any ideas???

Source: http://www.livejournal.com/community/csharp/41546.html

Date: 11/18/05 (SQL Server)    Keywords: database, sql, security, google

Source: http://www.livejournal.com/community/sqlserver/37213.html