FreePgs.com Forum

FreePgs Related => Support Requests => Topic started by: sixthcrusifix on November 30, 2005, 01:13:56 AM

Title: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:13:56 AM
$SQL1="UPDATE 'userbase' SET password = \"$NEW_PW\" WHERE username =\"$lostpw\"";


what's wrong with that? I keep getting the die message!

here's the whole script. I KNOW that just setting somone's password to a random number is a bit cheap but they can change it and I'm lazy:
(userbase is the table, DATA is the Database)\

EDIT: Turns out onone of it works anymore since I re-uploaded it!?!??!?! I get this stuff:  Resource id #4Resource id #4 Resource id #3


<?php
if(isset($lostpw))
{
srand(microtime() * 1000000);
$NEW_PW rand(100000,999999);

$conn=@mysql_connect("localhost","sixthcrusifix","*******")or die("Sorry, Could Not Connect");
$DTB=@mysql_select_db(DATA,$conn) or die("Could Not Select Database");

$SQL1="UPDATE 'userbase' SET password = \"$NEW_PW\" WHERE username =\"$lostpw\"";

$result1=mysql_query($SQL1,$conn) or die("SORRY, password could not be reset");

$SQL="SELECT email
FROM `userbase`
WHERE username = \"
$lostpw\"";
$result=mysql_query($SQL,$conn) or die("SORRY, We can't find your new password");
$EMAIL=$result;
$SQL2="SELECT password
FROM `userbase`
WHERE username = \"
$lostpw\"";
$result2=mysql_query($SQL2,$conn)or die("SECOND QUERY FAILED");
$LPW=$result2;
$msg="$lostpw, your new password is $LPW. You may change your password in the User Control Panel";
mail($EMAIL,"Lost Password",$msg);
$ALERT1="<script> alert(\"Your Email Has Been Sent to $EMAIL\")</script>";
}
else{ 
$ALERT1="You Did NOt Define  A Username!"; }
#############################################################################################################
//The Below is the HEAD!

echo($ALERT1);

#############################################################################################################
//The below is the body
#############################################################################################################

include "defaultcss.php";
echo (
"
$BG_black

$table_1
$center
$main_category
$category

$tr
$content
$TS4
$ALERT1
$br 
$LPW$LPW
$EMAIL
$TS_end1
$bottom
$br
$tr_end
$main_bottom

$div_end
$table_end
"
)
?>

<?
######################################################################################################

//Below is the MAIN MENU (The default menu for ALL pages)
######################################################################################################
include "default_menu.php";
?>


Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 01:26:15 AM
Try this instead :

$SQL1 = 'UPDATE userbase SET password = \''.$NEW_PW.'\' WHERE username = \''.$lostpw.'\'';
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:29:15 AM
Quote from: webzone on November 30, 2005, 01:26:15 AM
Try this instead :

$SQL1 = 'UPDATE userbase SET password = \''.$NEW_PW.'\' WHERE username = \''.$lostpw.'\'';

1. COOL! That worked.

BUT My otherstuff is wrong now. . . instead of the text from the fields it says "Resource ID#3" But it used to work. . what's resource ID???
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 01:31:01 AM
$SQL1 ="UPDATE userbase SET password ='$NEW_PW' WHERE username = '$lostpw' '';

You dont need double quotes on the variables.. just the outside, which makes it easier.

And if you want to actually see the error use this:

$result1=mysql_query($SQL1,$conn) or die(mysql_error());
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:37:46 AM
Quote from: ghost on November 30, 2005, 01:31:01 AM
$SQL1 ="UPDATE userbase SET password ='$NEW_PW' WHERE username = '$lostpw' '';

You dont need double quotes on the variables.. just the outside, which makes it easier.

And if you want to actually see the error use this:

$result1=mysql_query($SQL1,$conn) or die(mysql_error());
Oh cool thanks.

I still don't knwo about the other stuff not working anymor ethough.
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 01:42:57 AM
Looks like it was just the way the line was written with those quotes.

mysql_error() will most likely be helpful in the future though.
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:47:13 AM
Quote from: ghost on November 30, 2005, 01:42:57 AM
Looks like it was just the way the line was written with those quotes.

mysql_error() will most likely be helpful in the future though.


Now I'm getting a PHP error (WARNING blah blah, function mail expects perameter 1 to ba a string


Instead of getting the password and username it's returning resource id#3 for the password and resource id#4
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 01:55:41 AM
It would help if you copied and pasted the error as it shows up on the page. (Same page as before I'm guessing too?)
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:57:42 AM
Quote from: ghost on November 30, 2005, 01:55:41 AM
It would help if you copied and pasted the error as it shows up on the page. (Same page as before I'm guessing too?)

yeah the source code is in the first post. I tried to copy it but I have to get rid of that javascript pop-up box.
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 01:58:52 AM
Javascript popup box?
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 01:59:58 AM
Quote from: ghost on November 30, 2005, 01:58:52 AM
Javascript popup box?

I made an alert and I forgot to take it down so I could copy the error. I figured out the prioblem.

Instead of getting the EMAIL from the EMAIL part of the table, it just gets "resource id#" everything else works. . .
Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 02:01:18 AM
Quote from: ghost on November 30, 2005, 01:31:01 AM
$SQL1 ="UPDATE userbase SET password ='$NEW_PW' WHERE username = '$lostpw' '';

You dont need double quotes on the variables.. just the outside, which makes it easier.

My solution didn't use double quotes, but single quotes, this is why they had to be escaped. I agree that your solution is simpler, but mine is faster to process (single quotes are always faster).
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:02:07 AM

Resource id #3Resource id #3 Lost Password sixthcrusifix, your new password is 326671. You may change your password in the User Control Panel
^That's an echo of the variables^


Warning: mail() expects parameter 1 to be string, resource given in /fpgs/fpgshttpd/sixthcrusifix/lostpw.php on line 30
alert("Your Email Has Been Sent to Resource id #3")

Instead of RESOURCE ID#3 it should say an email adress
Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 02:04:46 AM
I think I found your problem.

You are doing a query and using the value mysql_query returned directly. mysql_query only returns a resource identifier that is designed to be fetched afterwards.

Try this :
$query2=mysql_query($SQL2,$conn)or die("SECOND QUERY FAILED");
$result2=mysql_fetch_object($query2);
$LPW=$result2->password;

To free memory you could also add :
mysql_free_result($query2);
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:08:17 AM
Quote from: webzone on November 30, 2005, 02:04:46 AM
I think I found your problem.

You are doing a query and using the value mysql_query returned directly. mysql_query only returns a resource identifier that is designed to be fetched afterwards.

Try this :
$query2=mysql_query($SQL2,$conn)or die("SECOND QUERY FAILED");
$result2=mysql_fetch_object($query2);
$LPW=$result2->password;

To free memory you could also add :
mysql_free_result($query2);

okay lemmie try that ./ . .
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:11:15 AM
Change to these? :: $SQL , $SQL2 ::

$SQL="SELECT email FROM `userbase` WHERE username = '$lostpw'";

$SQL2="SELECT password FROM `userbase` WHERE username = '$lostpw'";
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:12:34 AM
I messed up . . .


Notice: Undefined variable: query2 in /fpgs/fpgshttpd/sixthcrusifix/lostpw.php on line 18

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /fpgs/fpgshttpd/sixthcrusifix/lostpw.php on line 18

Notice: Undefined variable: result2 in /fpgs/fpgshttpd/sixthcrusifix/lostpw.php on line 19

Notice: Trying to get property of non-object in /fpgs/fpgshttpd/sixthcrusifix/lostpw.php on line 19
Lost Password sixthcrusifix, your new password is 478152. You may change your password in the User Control Panel alert("Your Email Has Been Sent to ")
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:14:57 AM
Quote from: webzone on November 30, 2005, 02:01:18 AM
Quote from: ghost on November 30, 2005, 01:31:01 AM
$SQL1 ="UPDATE userbase SET password ='$NEW_PW' WHERE username = '$lostpw' '';

You dont need double quotes on the variables.. just the outside, which makes it easier.

My solution didn't use double quotes, but single quotes, this is why they had to be escaped. I agree that your solution is simpler, but mine is faster to process (single quotes are always faster).

Single quotes shouldn't need to be escaped?
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:17:17 AM
Quote from: ghost on November 30, 2005, 02:14:57 AM
Quote from: webzone on November 30, 2005, 02:01:18 AM
Quote from: ghost on November 30, 2005, 01:31:01 AM
$SQL1 ="UPDATE userbase SET password ='$NEW_PW' WHERE username = '$lostpw' '';

You dont need double quotes on the variables.. just the outside, which makes it easier.

My solution didn't use double quotes, but single quotes, this is why they had to be escaped. I agree that your solution is simpler, but mine is faster to process (single quotes are always faster).

Single quotes shouldn't need to be escaped?


Okay I did it and I'm not getting ENY error messages !! (I un escaped them and erased some typos) Now I just have to see if I actually get the emails... so far it seems good though.

herers the code:



<?php
if(isset($lostpw))
{
srand(microtime() * 1000000);
$NEW_PW rand(100000,999999);

$conn=@mysql_connect("localhost","sixthcrusifix","*******")or die("Sorry, Could Not Connect");
$DTB=@mysql_select_db(DATA,$conn) or die("Could Not Select Database");
$SQL1 'UPDATE userbase SET password = \''.$NEW_PW.'\' WHERE username = \''.$lostpw.'\'';

$result1=mysql_query($SQL1,$conn) or die("SORRY, password could not be reset");

$SQL="SELECT email
FROM `userbase`
WHERE username = '
$lostpw'";

$query=mysql_query($SQL,$conn)or die("SECOND QUERY FAILED");
$result=mysql_fetch_object($query);
$EMAIL=$result->email;


$SQL2="SELECT password
FROM `userbase`
WHERE username = '
$lostpw'";

$query2=mysql_query($SQL2,$conn)or die("SECOND QUERY FAILED");
$result2=mysql_fetch_object($query2);
$LPW=$result2->password;



$msg="$lostpw, your new password is $LPW. You may change your password in the User Control Panel";
echo(
"$EMAIL  Lost Password $msg");
mail($EMAIL,"Lost Password",$msg);
$ALERT1="<scr#ipt> alert(\"Your Email Has Been Sent to $EMAIL\")</script>";
}


else{ 
$ALERT1="You Did NOt Define  A Username!"; }


#############################################################################################################
//The Below is the HEAD!

echo($ALERT1);

#############################################################################################################
//The below is the body
#############################################################################################################

include "defaultcss.php";
echo (
"
$BG_black

$table_1
$center
$main_category
$category

$tr
$content
$TS4
$ALERT1
$br 
$LPW$LPW
$EMAIL
$TS_end1
$bottom
$br
$tr_end
$main_bottom

$div_end
$table_end
"
)
?>

<?
######################################################################################################

//Below is the MAIN MENU (The default menu for ALL pages)
######################################################################################################
include "default_menu.php";
?>

Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 02:17:57 AM
@sixthcrusifix

if the variable is undefined, it probably means that the query failed. i'm going to look closer to the code.

@ghost

to include a single quote inside a string delimited itself by single quotes, I need to escape them.
ex : $variable = 'UPDATE userbase SET password = \' (escaped single quote) ' (delimiter)
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:19:40 AM
Quote from: webzone on November 30, 2005, 02:17:57 AM
@sixthcrusifix

if the variable is undefined, it probably means that the query failed. i'm going to look closer to the code.

@ghost

to include a single quote inside a string delimited itself by single quotes, I need to escape them.
ex : $variable = 'UPDATE userbase SET password = \' (escaped single quote) ' (delimiter)

Yeah I copied and pasted and forgot to change the name. I JUST TESTED IT AND IT WORKS! YAY! :D :D :D  :D :D :D

I got the email with the new PW... the only thing to check is. . . if the password works! LOLL
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:20:38 AM
@webzone

Ok, yeah if you do it that way you do need it, although I still think it's alot easier just to put double quotes for the outside.
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:22:22 AM
Just wondering what you are making this in? Your code is very colourful lol.. (compared to notepad anyways)
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:24:51 AM
Quote from: ghost on November 30, 2005, 02:22:22 AM
Just wondering what you are making this in? Your code is very colourful lol.. (compared to notepad anyways)

That's just how it looks when you put it in the code tag on the forum.


NOT ONLY does it work perfectly but I added encryption so that I don't have to chang the login script. The new passwords are sent to the database encrypted and given to the user unencrypted. And I logged in and it works. :D
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:28:42 AM
Quote from: sixthcrusifix on November 30, 2005, 02:24:51 AM
Quote from: ghost on November 30, 2005, 02:22:22 AM
Just wondering what you are making this in? Your code is very colourful lol.. (compared to notepad anyways)

That's just how it looks when you put it in the code tag on the forum.


NOT ONLY does it work perfectly but I added encryption so that I don't have to chang the login script. The new passwords are sent to the database encrypted and given to the user unencrypted. And I logged in and it works. :D

And thats how it should be lol.. not sure what you would want with un-encrypted passwords in the database. (Although mine has both.. lol)
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:43:21 AM
Quote from: ghost on November 30, 2005, 02:28:42 AM
Quote from: sixthcrusifix on November 30, 2005, 02:24:51 AM
Quote from: ghost on November 30, 2005, 02:22:22 AM
Just wondering what you are making this in? Your code is very colourful lol.. (compared to notepad anyways)

That's just how it looks when you put it in the code tag on the forum.


NOT ONLY does it work perfectly but I added encryption so that I don't have to chang the login script. The new passwords are sent to the database encrypted and given to the user unencrypted. And I logged in and it works. :D

Before when you lost your password the new password was just sent to the database. But that would cause problems now because the login form used md5 and it would end up not matching. So yes, I don't know what I was thinking not encrypting them. :P
And thats how it should be lol.. not sure what you would want with un-encrypted passwords in the database. (Although mine has both.. lol)
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 02:46:37 AM
And now back to breaking my own stuff, if you have anymore questions just post again.. I should be here for awhile longer.
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 02:55:52 AM
Quote from: ghost on November 30, 2005, 02:46:37 AM
And now back to breaking my own stuff, if you have anymore questions just post again.. I should be here for awhile longer.


look at this, it's working on one page and not the other!?!??!

<?php
if( (!$username) or (!$password) )
{
header("Location:$HTTP_REFERER") ; exit();
}


$conn = @mysql_connect("localhost","sixthcrusifix","") or die("Sorry Login Failed 1.1");

$rs = @mysql_select_db("MAIN",$conn) or die("Sorry Login Failed 1.2");

$sql="select * from userbase where username=\"$username\" and password=\"$password\"";

$rs=mysql_query($sql,$conn) or die(mysql_error());

$num mysql_numrows($rs);

$SQL2="SELECT type
FROM `userbase`
WHERE username = '
$username'";

$query2=mysql_query($SQL2,$conn)or die("SECOND QUERY FAILED");
$result2=mysql_fetch_object($query2);

$QWiuwq08wqjwqQW0_S=$result2->type;

if( (
$num != 0) && ($QWiuwq08wqjwqQW0_S !="user") )
{
$msg "Welcom $username - You were succesfully logged in!, TYPE: $QWiuwq08wqjwqQW0_S ";
session_start();
session_register("logged");
$logged 1;
setcookie("user"$usernametime()+2592000);
$user=$username;
}
else

if (
$QWiuwq08wqjwqQW0_S="user")
{echo(
"YOU ARE NOT AN ADMINISTRATOR! Your Username has been sent to the administrater and you have been logged for trying to log into the admin CP!");}

$msg "Sorry your username or password was incorrect! <a href=\"login.php\">Retry?</a>";
}
?>



<html>
<head><title>Log In to The Final Destiny!</title></head>
<body>




<body bgcolor="000000">
<div align="center"><img src="http://freepgs.com/sixthcrusifix/HEAD.jpg"></div>
<table width="500" overflow="480" align="left"

BACKGROUND="http://freepgs.com/sixthcrusifix/MAIN/images/Template/BG_

MAIN.gif" cellspacing="0" cellpadding="0">
<div align="center"><tr>
<td><img

src="http://freepgs.com/sixthcrusifix/MAIN/images/Template/TOP_MAIN.gif">

</tr></td>

</tr>
<td><img

src="http://freepgs.com/sixthcrusifix/MAIN/images/Template/TD_TOP.gif"></t

r></td>

<tr>
<td  width="468"

BACKGROUND="http://freepgs.com/sixthcrusifix/MAIN/images/Template/TD_

BG.gif">
<font size="4"><font color="ffffff"><div align="center">
<?php echo($msg); ?>
</font></font></div>
</tr></td>

</tr>
<td><img

src="http://freepgs.com/sixthcrusifix/MAIN/images/Template/TD_BOTTOM.gif

"></tr></td>
<br>
</tr>

<tr>
<td><img

src="http://freepgs.com/sixthcrusifix/MAIN/images/Template/BOTTOM_MAIN.

gif" name="test"></tr></td>
</div>
</table>




it works on its page in my main directory but not in the new folder I put it in!


this is the error message:


Table 'MAIN.userbase' doesn't exist



NVM I'm an idiot. There really is no database named MAIN. . . I renamed it to "DATA" LOLL
Title: Re: ANybody good at SQL? I keep messing up
Post by: ghost on November 30, 2005, 03:04:20 AM
$rs = @mysql_select_db("MAIN",$conn) or die("Sorry Login Failed 1.2");

$sql="select * from userbase where username=\"$username\" and password=\"$password\"";


change to ::

$rs = @mysql_select_db(DATA,$conn) or die("Sorry Login Failed 1.2");

$sql="select * from userbase where username='$username' and password='$password'";


(I'm not completely sure about that first line but try it anyways.)
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 03:05:10 AM
Quote from: ghost on November 30, 2005, 03:04:20 AM
$rs = @mysql_select_db("MAIN",$conn) or die("Sorry Login Failed 1.2");

$sql="select * from userbase where username=\"$username\" and password=\"$password\"";


change to ::

$rs = @mysql_select_db(DATA,$conn) or die("Sorry Login Failed 1.2");

$sql="select * from userbase where username='$username' and password='$password'";


oh.. you figured it out too. geeze I'm a dumby.

(I'm not completely sure about that first line but try it anyways.)
Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 03:09:28 AM
be careful... if your code is the same as what you posted, wouldn't it be vulnerable to SQL injection attacks?

i was always told and i always sanitized my variables with addslashes() before using them. unless there is something I missed in the code
Title: Re: ANybody good at SQL? I keep messing up
Post by: Ben on November 30, 2005, 04:04:27 AM
Oh no! You made me get out the "Writing Secure PHP" article I printed out. Since I don't remember the URL, I'll type it here:

QuoteSQL Injection

One of PHP's greatest strengths is the ease with which it can communicate with databases, most notable MySQL. Many people make extensive use of this, and a great many sites, including this one, rely on databases to function.

However, as you would expect, with that much power there are potentially huge security problems you can face. Fortunately, there are plenty of solutions. The most common security hazard faced when interacting with a database is that of SQL Injection - when a user uses a security glitch to run SQL queries on your database.

Let's use a common example. Many login systems feature a line that looks a lot like this when checking the username and password entered into a form by a user against a database of valid username and password combinations, for example to control access to an administration area:

$check = mysql_query("SELECT Username, Password, UserLevel FROM Users WHERE Username = '".$_POST['username'].'" and Password = '".$_POST['password']."'");
Look familiar? It may well do. And on the face of it, the above does not look like it could do much damage. But let's say for a moment that I enter the following into the "username' input box in the form and submit it:

' OR 1=1 #
The query that is going to be executed will now look like this:

SELECT Username, Password FROM Users WHERE Username = '' OR 1=1#' and Password = ''
The hash symbol (#) tells MySQL that everything following it is a commend and to ignore it. So it will actually only execute the SQL up to that point. As 1 always equals 1, the SQL will return all of the usernames and passwords from the database. And as the first usename and password combination in most user login database is the admin user, the person who simply entered a few symbols in a username box is now logged in as your website administrator, with the same powers they would have if they actually knew the username and password.

With a little creativity, the above can be exploited further, allowing a user to create their own login account, read credit card numbers or even wipe a database clean.

Fortunately, this type of vulnerability is easy enough to work around. By checking for apostrophes in the items we enter into the database, and removing or neutralising them, we can prevent anyone from running their own SQL code on our database. The function below would do the trick:

function make_safe($variable) {
  $variable = addslashes(trim($variable));
  return $variable;
}
Now to modify our query. Instead of using _POST variables as int he query above, we now run all user data through the make_safe function, resulting in the following code:

$username = make_safe($_POST['username']);
$password = make_safe($_POST['password']);
$check = mysql_query("SELECT Username, Password, UserLevel FROM Users WHERE Username = '".$username."' and Password = '".$password."'");
Now, if a user entered the malicious data above, the query will look like the following, which is perfectly harmless, The following query will select from a database where the username is equal to "\'OR 1=1 #".

SELECT Username, Password, UserLevel FROM Users WHERE Username ='\' OR 1=1 #' and Password = ''
Now, unless you happen to have a user with a very unusual username and a blank password, your malicious attacker will not be able to do any damage at all. It is important to check all data passed to your database like this, however secure you think it is. HTTP Headers sent from the user can be faked. Their referral address can be faked. Their browsers User Agent string can be faked. Do not trust a single piece of data sent by the user, though, and you will be fine.

From an article titled "Writing Secure PHP", July 16, 2004 from an unknown source. If anyone knows the source, PM me and I'll post it here. :-)
Title: Re: ANybody good at SQL? I keep messing up
Post by: webzone (archived) on November 30, 2005, 04:07:20 AM
does your article come from this site? http://www.ilovejackdaniels.com/security/writing-secure-php/2/
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 04:11:49 AM
Quote from: nq2h on November 30, 2005, 04:04:27 AM
Oh no! You made me get out the "Writing Secure PHP" article I printed out. Since I don't remember the URL, I'll type it here:

QuoteSQL Injection

One of PHP's greatest strengths is the ease with which it can communicate with databases, most notable MySQL. Many people make extensive use of this, and a great many sites, including this one, rely on databases to function.

However, as you would expect, with that much power there are potentially huge security problems you can face. Fortunately, there are plenty of solutions. The most common security hazard faced when interacting with a database is that of SQL Injection - when a user uses a security glitch to run SQL queries on your database.

Let's use a common example. Many login systems feature a line that looks a lot like this when checking the username and password entered into a form by a user against a database of valid username and password combinations, for example to control access to an administration area:

$check = mysql_query("SELECT Username, Password, UserLevel FROM Users WHERE Username = '".$_POST['username'].'" and Password = '".$_POST['password']."'");
Look familiar? It may well do. And on the face of it, the above does not look like it could do much damage. But let's say for a moment that I enter the following into the "username' input box in the form and submit it:

' OR 1=1 #
The query that is going to be executed will now look like this:

SELECT Username, Password FROM Users WHERE Username = '' OR 1=1#' and Password = ''
The hash symbol (#) tells MySQL that everything following it is a commend and to ignore it. So it will actually only execute the SQL up to that point. As 1 always equals 1, the SQL will return all of the usernames and passwords from the database. And as the first usename and password combination in most user login database is the admin user, the person who simply entered a few symbols in a username box is now logged in as your website administrator, with the same powers they would have if they actually knew the username and password.

With a little creativity, the above can be exploited further, allowing a user to create their own login account, read credit card numbers or even wipe a database clean.

Fortunately, this type of vulnerability is easy enough to work around. By checking for apostrophes in the items we enter into the database, and removing or neutralising them, we can prevent anyone from running their own SQL code on our database. The function below would do the trick:

function make_safe($variable) {
  $variable = addslashes(trim($variable));
  return $variable;
}
Now to modify our query. Instead of using _POST variables as int he query above, we now run all user data through the make_safe function, resulting in the following code:

$username = make_safe($_POST['username']);
$password = make_safe($_POST['password']);
$check = mysql_query("SELECT Username, Password, UserLevel FROM Users WHERE Username = '".$username."' and Password = '".$password."'");
Now, if a user entered the malicious data above, the query will look like the following, which is perfectly harmless, The following query will select from a database where the username is equal to "\'OR 1=1 #".

SELECT Username, Password, UserLevel FROM Users WHERE Username ='\' OR 1=1 #' and Password = ''
Now, unless you happen to have a user with a very unusual username and a blank password, your malicious attacker will not be able to do any damage at all. It is important to check all data passed to your database like this, however secure you think it is. HTTP Headers sent from the user can be faked. Their referral address can be faked. Their browsers User Agent string can be faked. Do not trust a single piece of data sent by the user, though, and you will be fine.

From an article titled "Writing Secure PHP", July 16, 2004 from an unknown source. If anyone knows the source, PM me and I'll post it here. :-)

Cool I'll probably use it. Although I don't see anybody caring enough to see what' in my databases. If I did I would have rememberd to erase my password when I posted the code . . . . . oops...
Title: Re: ANybody good at SQL? I keep messing up
Post by: Ben on November 30, 2005, 04:19:21 AM
webzone: Most likely, I think I printed the article out a year ago, so I don't remember. That page is word-for-word what I have posted.

sixthcrusifix: You never know what kind of idiots you'll have on your site. You wouldn't want stuff like email addresses being exposed to the public, now would you?
Title: Re: ANybody good at SQL? I keep messing up
Post by: sixthcrusifix on November 30, 2005, 04:23:23 AM
Quote from: nq2h on November 30, 2005, 04:19:21 AM
webzone: Most likely, I think I printed the article out a year ago, so I don't remember. That page is word-for-word what I have posted.

sixthcrusifix: You never know what kind of idiots you'll have on your site. You wouldn't want stuff like email addresses being exposed to the public, now would you?

Guess not... that's why "you agree that It's not my fault" Is repeated in 72 different ways in my TOS. XD