ANybody good at SQL? I keep messing up

[webzone (archived)]

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

[Ben]

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:

SQL 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. :-)

[webzone (archived)]

does your article come from this site? http://www.ilovejackdaniels.com/security/writing-secure-php/2/

[sixthcrusifix]

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:

SQL 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...

[Ben]

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?

[sixthcrusifix]

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