Simple mysql query..

[rpope904]

I am having some issues with a script, basically, it just verifies if an account exists, if so tells the user, otherwise adds the data to the database.. I have created the database, username, etc..  The table, users also exists with the correct fields, I manually added some data into them for testing to see if it maybe had to have something in it.. Can you see my error:

 

Code:

 

<?php
$ni = $_POST['nickname'];
$ch = $_POST['channel'];
$nipass = $_POST['nickpassword'];
$chpass = $_POST['chanpassword'];
$description = $_POST['description'];
$HOST = 'localhost';
$DBUSER = 'rpope904_x10chat';
$PASS = '*******';
$DATABASE = 'rpope904_x10chat';


$connection = mysql_connect($HOST, $DBUSER, $PASS)
or die("Cannot connect to database server!");
$db = mysql_select_db($DATABASE, $connection)
or die("Cannot select database!");

$sql = "SELECT nickname FROM users
WHERE username = $ni";

$sql2 = "SELECT channel FROM users
WHERE email = $ch";

[i][b]$result = mysql_query($sql)
or die ("Can not check username. (DB ERROR));

$result2 = mysql_query($sql2)
or die ("Can not check channel. (DB ERROR)");[/b][/i]

$num = mysql_num_rows($result);
$num2 = mysql_num_rows($result2);

if ($num == 1)
{
echo "Error, user already exists!";
echo "
<a href=\"signup.php\">Back to signup..</a>";
}
elseif ($num2 == 1){
echo "Someone has already registered that channel.";
echo "
<a href=\"signup.php\">Back to signup..</a>";
}
else
{

$query = "INSERT INTO users (nickname,channel,email,nickpassword,chanpassword,description)
VALUES ('$_POST[nickname]','$_POST[channel]','$_POST[email]','$_POST[nickpassword]','$_POST[chanpassword]','$_POST[description]')";
$resultB = mysql_query($query,$connection) or die ("Coundn't execute query.");
echo "Sucess! We have created your personal site.<br>";

 

 

When I go to the page, it returns:

 

Can not check username. (DB ERROR)

 

Like I said, the db has info in it.. Here's a PHPMyAdmin output of the database, and table 'users':

 

Full  Texts  id nickname channel nickpassword chanpassword email                          description

Edit Delete 1 fktest testroom4 testpass4  chanpass4  rpope904@comcast.net just a test room

[tippy_102]

Lets see what the real error is. Put this in your result line:

or die ("couldn't connect to db: " . mysql_error());

[rpope904]

couldn't connect to db: Unknown column 'username' in 'where clause'

[therealwesfoster]

Change this:

 

$sql = "SELECT nickname FROM users

WHERE username = $ni";

 

to

 

$sql = "SELECT nickname FROM users

WHERE nickname = $ni";

[rpope904]

Yeah, I caught that one right after I posted it.. now its giving:

 

couldn't connect to db: Unknown column 'fktest' in 'where clause'

 

fktest is the nickname, and it should be searching under the column nickname..

[therealwesfoster]

Change your script to this and see what happens:

 

<?php
$ni = mysql_real_escape_string($_POST['nickname']); // the mysql_real_escape_string() function prevents SQL injection
$ch = mysql_real_escape_string($_POST['channel']);
$nipass = mysql_real_escape_string($_POST['nickpassword']);
$chpass = mysql_real_escape_string($_POST['chanpassword']);
$description = mysql_real_escape_string($_POST['description']);
$HOST = 'localhost';
$DBUSER = 'rpope904_x10chat';
$PASS = '*******';
$DATABASE = 'rpope904_x10chat';


$connection = mysql_connect($HOST, $DBUSER, $PASS) or die("Cannot connect to database server!");
$db = mysql_select_db($DATABASE, $connection) or die("Cannot select database!");

$sql = "SELECT nickname FROM users
WHERE nickname = $ni";

$sql2 = "SELECT channel FROM users
WHERE email = $ch";

$result = mysql_query($sql) or die ("Can not check username.<br />".mysql_error());
$result2 = mysql_query($sql2) or die ("Can not check channel.<br />".mysql_error());

$num = mysql_num_rows($result);
$num2 = mysql_num_rows($result2);

if ($num > 0)
{
   echo "Error, user already exists!<br />";
   echo "<a href="signup.php\">Back to signup..</a>";
}
elseif ($num2 > 0)
{
   echo "Someone has already registered that channel.<br />";
   echo "<a href=\"signup.php\">Back to signup..</a>";
}
else
{
  $query = "INSERT INTO users (nickname,channel,email,nickpassword,chanpassword,description)
  VALUES ('{$_POST['nickname']}','{$_POST['channel']}','{$_POST['email']}','{$_POST['nickpassword']}','{$_POST['chanpassword']}','{$_POST['description']}')";
$resultB = mysql_query($query,$connection) or die ("Coundn't execute query.<br />".mysql_error());
echo "Sucess! We have created your personal site!<br />";
}

 

Let me know what happens

[AnnieKay]

Sorry rpope, I'm not really posting an answer to your question - but was intrigued by what Wesf wrote above: do you already use the mysql_real_escape_string() in your own coding to prevent injections?

 

I'm new to all this, but learning loads just from reading replies to posts I thought I knew the answers to in the first place!

Tks, Annie