OK there are alot of things wrong with this code so I've rewritten it to be up to date and not at all dangerous (mostly).
I'm not hashing the passwords here like you really should but you can do it quite simply with a bit of reading ( try this: Secure hash and salt for PHP passwords )
For a comprehensive list of the exact problems and solutions to those problems, take a look at @Bondye's post or the comments on the OP's question.
Disclaimer: not tested so might have a few syntax errors. this is still not fantastic but it's a much better starting point than the original code. See below for a list of what I've changed and why.
Here goes...
<?php
$host = "localhost";
$database = "yourdatabase";
$username_db = "root";
$password_db = "databasepassword";
$con = mysqli_connect($hostname, $username_db, $password_db, $database) or die(mysqli_error($con));
$username = $_POST['userid'];
$newpassword = $_POST['pword'];
$confirmnewpassword = $_POST['pword2'];
if($newpassword == $confirmnewpassword)
{
//password & password confirm match, do the update
$query = sprintf("UPDATE login SET pword=%s WHERE username=%s",
mysql_real_escape_string($newpassword),
mysql_real_escape_string($username));
$sql = mysqli_query($query, $con) or die(mysqli_error($con);
if($sql)
{
echo "Congratulations, password successfully changed!";
}
else
{
//sql error or update didn't work?
echo 'generic failure message';
}
}
else
{
//new password and confirm password weren't the same.
echo "New password and confirm password must be the same!";
}
?>
Changes:
Removed the query to look for a username - personal choice really but I don't see the point in querying the DB to see if the user exists when you are going to be doing an implicit search for that user in the update
query. Secondly, you should have logged them in before they can access this script, so there should be no question about whether the user exists or not.
Changed mysql
functions to their mysqli
equivelants.
Simplified and cleaned up the mess of if
tests and put validation before the query itself. This is better as you shouldn't tell your users what you have in your database as this is useful information for attackers and not at all useful for users who should already be logged in by this point.
Hope this helps and open to corrections.