8 replies to this topic

[Hostile]

I'm attempting to access the Revora Database read only login and despite the obvious securty issues, I'll not keep this page live for more than 30 secs at a time. It's not currently live.

But I'm trying to create an SQL query so I can pull from two tables at once from the database for my site. I'm trying to eventually pull a logged in USER's avatar and some other user info based on a query.

Here is my posted code. Understand this is just testing code not live and full of security issues, I'm just trying to understand how to make a valid SQL statement pulling from multiple tables.

<?
if ((!$_POST[username]) || (!$_POST[password])) {
header ( "Location: show_login.php");
exit;

$db_name = "*****";

$table_user = "*****";
$table_pw = "*****";

$connection = @mysql_connect("localhost", "*****", "*****") or die(mysql_error());

$db = @mysql_select_db($db_name, $connection)

$sql = "SELECT * FROM $table_user WHERE username = '$_POST[username]' AND FROM $table_pw WHERE password = password('$_POST[password]')" 
 or die(mysql_error());
 
$result = @mysql_query($sql,$connection) or die(mysql_error());
$num = mysql_num_rows($result);

if ($num !=0) {$msg = "<p>Authorized</p>";}
else {header("location: show_login.html};
exit;
?>

$sql = "SELECT * FROM $table_user WHERE username = '$_POST[username]' AND FROM $table_pw password = password('$_POST[password]')"
or die(mysql_error());

I of course hidden sensitve info with "*" I was hoping someone can guide me correctly. Bold text shows the invalid code... This is an action script from the submit of a logg-in text field with password.

[DCoder]

SELECT t1.column1, t1.column2, t1.column3, t2.column1, t2.column2 -- and so on FROM table1 t1, table2 t2
WHERE table1.username = 'user' AND table2.password = 'pass'

You need to prefix each column with the proper table name or an alias. In my example, table1/table2 are table names, t1/t2 are aliases.
You should also use aliases on columns (syntax: table.column columnalias) if you need to fetch two columns like t1.id and t2.id , to avoid "Duplicate key in column x" errors.

Also, in

$sql = "SELECT * FROM $table_user WHERE username = '$_POST[username]' AND FROM $table_pw WHERE password = password('$_POST[password]')"
or die(mysql_error());

$result = @mysql_query($sql,$connection) or die(mysql_error());

you don't need error handling on the first statement, it's just a string assignment.

[Kravvitz]

Try this:

$sql = "SELECT * FROM $table_user u, $table_pw p  WHERE u.username = '$_POST[username]' AND  p.password = password('$_POST[password]')"
or die(mysql_error());

Why are user names and passwords stored in different tables anyway?

[Mastermind]

It's the way IPB 2.x does things.

[Blodo]

Is it a mysql error?

$sql = "SELECT * FROM [b]`[/b]$table_user[b]`[/b] (...)

Table names have to be enclosed in tildes ( ` )

[Mastermind]

Table names do not have to be enclosed in back ticks or any form of quotes.

function dbgetuser($username)
{
	$usernames = $this->quote_smart($username);
	$prefix=$this->dbprefix;
	$sql = "SELECT id from ".$prefix."members where name=$usernames";
	$result = mysql_query($sql, $this->dbconn);
	if(mysql_num_rows($result)==1)
	{
  $id = mysql_fetch_row($result);
  return $id[0];
	}
	return -1;
}
function dbcheckuser($userid, $password, &$IPBpass)
{
    $prefix=$this->dbprefix;
	$userids = $this->quote_smart($userid);
	$sql = "SELECT converge_pass_salt from ".$prefix."members_converge where converge_id=$userids";
	$result = mysql_query($sql, $this->dbconn);
	if(mysql_num_rows($result)==1)
	{
  $salt = mysql_fetch_row($result);
  $salt = $salt[0];
  $IPBpass = md5(md5($salt).md5($password));
  $sql = "SELECT * from ".$prefix."members_converge where converge_pass_hash='$IPBpass'";
        $result = mysql_query($sql, $this->dbconn);
        if(mysql_num_rows($result)==1)
        {
            //valid user return true
            return true;
        }
	}
	return false;
}

[Blodo]

It's good practice to enclose them to avoid sql syntax errors

[Mastermind]

Another newer slightly improved version...

function check_user_login($username, $MD5password)
	{
  $usernames = quote_smart($username);
  $passwords = quote_smart($MD5password);
  if(!$this->dbconn)
  {
  	$this->dbconnect();
  }
  $query1 = "SELECT mgroup, id from ".$this->prefix."members where name=$usernames";
  $result1  = mysql_query($query1);
  if(mysql_num_rows($result1)==1)
  {
  	//echo 'name found';
  	$id = mysql_fetch_array($result1);
  	$ids = $id['id'];
  	$query2 = "SELECT converge_pass_salt, converge_pass_hash from ".$this->prefix."members_converge where converge_id=$ids";
  	$result2 = mysql_query($query2);
  	if(mysql_num_rows($result2)==1)
  	{
    //echo 'converge found';
    $converge = mysql_fetch_array($result2);
    if(md5(md5($converge['converge_pass_salt']).$MD5password)==$converge['converge_pass_hash'])
    {
    	$retarray = array("group"=>$id['mgroup'], "id"=>$id['id']);
    	return $retarray;
    }
  	}
  }
  return false;
	}

And a sample query using multiple tables:

$query = "SELECT topic.tid, topic.title, topic.description, topic.starter_id, topic.start_date, topic.posts, topic.state, topic.forum_id,
       post.post, member.name, member.title as memtitle, memberextra.avatar_location as avatar, memberextra.avatar_size, memberextra.avatar_type, forum.name as forumname
       from ".$this->prefix."topics as topic, ".$this->prefix."posts as post, ".$this->prefix."members as member, ".$this->prefix."forums as forum, ".$this->prefix."member_extra as memberextra
       where topic.forum_id=$forum_id AND topic.approved=1 AND post.topic_id=topic.tid
       AND post.new_topic=1 AND member.id=topic.starter_id AND forum.id=$forum_id AND memberextra.id=topic.starter_id
       order by topic.start_date DESC";

[Hostile]

I appreciate the new improved version MM and everyones contribution to the solution.. I'm still trying to make some of some of it and the next code I assume is to pull user info so I can present it?