MySQL check row

Question

So I am messing around with PHP and MySQL, I currently have a database with:

id | username | password

within it, I was wondering if there would be a way of checking if the username entered is the same as the password on the same row/ID (the ID is auto incrementing)

<form action="login.php" method="get">
    login > 
    <input name="log_username" type="text" />
    <input name="log_password" type="password" />
    <input id="submit" type="submit" />
</form>

I know its possible, but I - myself as a rookie with SQL and PHP cannot figure out ^^'

Thanks in Advance

EDIT:

For those interested this is my current register code (works brilliantly)

    <?php
$con=mysqli_connect(###########);
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

$sql="INSERT INTO users (username, password)
    VALUES ('$_POST[reg_username]','$_POST[reg_password]')";

    if (!mysqli_query($con,$sql)) {
        die('Error: ' . mysqli_error($con));
    }
    echo "1 record added";
    mysqli_close($con);
?>

I was reading through checking constants and using WHERE, but they don't seem to be able to do what I 'require' — Night, Jan 13 '14 at 18:32
Before you proceed any further, please read [How can I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) If you are using a tutorial to learn PHP/MySQL, put it away and find one more up to date. Your code is highly vulnerable to tampering via SQL injection. — Michael Berkowski, Jan 13 '14 at 18:34
Storing passwords as plain text, in conjunction with `VALUES ('$_POST[reg_username]','$_POST[reg_password]')` you are literally asking for an SQL injection/hack. DO read what Michael posted above, it may very well save your "you know what" someday. — Funk Forty Niner, Jan 13 '14 at 18:36
Your login action should be using `method='post'` rather than `get`. — Michael Berkowski, Jan 13 '14 at 18:41
On top of that, the POST values don't match the form's element names. So, I'm wondering, how could this ***"work brilliantly"***? `reg_username != log_username` — Funk Forty Niner, Jan 13 '14 at 18:43
@Fred-ii- The form is the OP's login form. The PHP is the OP's registration code. — Jessica, Jan 13 '14 at 18:48
Well, something doesn't add up. @Jessica obviously not full code. — Funk Forty Niner, Jan 13 '14 at 18:56
@Fred-ii- It makes perfect sense to me - he hasn't written any login code yet, because he can't figure out the query. He is simply showing the insert code that works. *shrug* — Jessica, Jan 13 '14 at 18:58
@Night This question is still open. Have you managed to solve this problem yet to your satisfaction? If not, please update the question and we'll try again to assist. — cssyphus, Jan 15 '14 at 18:44
No as people started to talk about SQL, so I flagged it for removal — Night, Jan 16 '14 at 15:05

Jessica · Answer 1 · 2014-01-13T18:46:22.277

SELECT * FROM users WHERE username = :username AND password = :password

Where password is the encrypted version of the password they submitted, because you're totally going to re-write this to encrypt passwords, and use prepared statements to remove your SQL injection vulnerability.

Creating a user login system is such a common thing that there are literally thousands of tutorials you could find on this topic, I suggest you do some basic research.

score -1 · Answer 2 · answered Jan 13 '14 at 18:38

-1

if ((isset($_GET['log_username']) && $_GET['log_username'] != "") && (isset($_GET['log_password']) && $_GET['log_password'] != "")
{
    $link = mysql_connect("localhost", "mysql_user", "mysql_password");
    mysql_select_db("database", $link);

    $query = "SELECT * FROM users WHERE username = " . $_GET['log_username'] . " AND password = " . $_GET['log_password'];
    $result = mysql_query($query);
    if (mysql_num_rows($result) < 1)
        echo("No record found");
}

This is the simplest way, but I suggest you to use PDO because mysql_query is deprecated.

answered Jan 13 '14 at 18:38

AleVale94

727
1
8
14

The OP _is_ using MySQLi, not `mysql_*()`. Additionally, the SQL here is syntactically invalid. `$_GET` will retrieve the values from a form `method='get'` but a POST is more appropriate than `GET` for this request. – Michael Berkowski Jan 13 '14 at 18:40
I didn't see the code since he has updated after I posted this answer. I used the method get because he uses get in the form, anyway I agree that in this case method post is better. – AleVale94 Jan 13 '14 at 18:42
That was `mysqli_query()` all along, never `mysql_query()`. – Michael Berkowski Jan 13 '14 at 18:44
The fact that he's using $_POST directly I think makes people assume it's MySQL, because if you know enough to use MySQLi, why are you using $_POST directly. – Jessica Jan 13 '14 at 18:47
I agree, I'm just saying I think when people see that, they block out the i in mysqli and assume the person is using mysql, because it's a "rookie" mistake. – Jessica Jan 13 '14 at 19:02

score -1 · Answer 3 · answered Jan 13 '14 at 18:46

Although you have requested a PHP solution, in real-world terms this might be better solved with some javascript/jQuery, because presenting the error to the user will not require a page refresh. For your peace of mind, this is what it would look like:

jsFiddle Demo

HTML:

<form id="myForm" action="login.php" method="get">
    login > 
    <input id="log_username" name="log_username" type="text" />
    <input id="log_password" name="log_password" type="password" />
    <input id="mySubmit" type="submit" />
</form>

jQuery:

$('#mySubmit').click(function(e) {
    e.preventDefault();
    var un = $('#log_username').val();
    var pw = $('#log_password').val();
    if ( un == pw ) {
        alert('Sorry, username/password cannot be identical');
    }else{
        $('#myForm').submit();
    }
});

Notes:

The above code uses jQuery, so you must reference the jQuery library, usually in the <head> tags, like this:

<head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
</head>

Note the use of e.preventDefault, which means: Don't do what a submit button would normally do (in other words, don't submit). We control the submission ourselves, manually.
This is how we use javascript/jQuery to manually submit: $('#myForm').submit();
Note that ID attributes were added to all elements that I needed to reference. Although it is possible to format jQuery to reference elements with or without IDs, this makes it much easier and there is no downside to doing so (although you must follow the rule that all elements must use unique IDs)
Also note that one should not use the word submit for an ID name for a submit button. Doing so may cause problems later.

That's so cool that you can see who downvotes your own posts, I don't have that ability. At what point do you get that? Oh, you mean you're assuming it was me because I commented on your post? Cool. — Jessica, Jan 13 '14 at 18:51

MySQL check row

3 Answers3