Which is the most secure and correct solution to display only one value from a single row using SELECT in PHP?

Question

My question is which solution do you think is best to use when wanting to retrieve only one value from a MySQL DB row.

For example, let's say we have the following:

-table "users" with three rows "username","password" and "status" AND three users "user1","user2" and "user3".

If I want to select only the status of one user (let's say user1) and set it to a variable, I will use:

$user_status = mysql_result(mysql_query("SELECT status FROM users WHERE username='user1'"),0);

I searched the net and I see that people use different methods of retrieving this type of info, such as setting LIMIT 1 inside the select code or by retrieving the whole users list and then sort the one that matches their needs.

I am wondering if my solution is the best and secure way (including security from SQL inject, keeping in mind that no $_GET method is used in the php code).

Maybe use both LIMIT 1 and the method I used above (for the code to require less resources and time to execute)?

What is the cocern with sql injection here? If you do not use user input then that should not be possible. Though I wonder how you want to do that, since somehow you have to specify which user you are currently testing... — arkascha, Jan 28 '14 at 12:53
Or you could use PDO and stored procedures....and do not use the mysql extension, go for mysqli or PDO — Hackerman, Jan 28 '14 at 12:54

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

From a database point of view the safest way is to have a unique key in the table you are selection from and retrieve the row via this key. In your example you could have a userID column that holds a unique ID for each user. If you query WHERE userID='...' the database guarantees you that there can only be one result row.

Edit: "Public opinion" suggested that I add two things.

Thou shall not use mysql_*! Why not use mysqli? You should not have to worry about its performance.
There is no reason to use LIMIT 1 if you are using proper database design - no reason at all. Its a bit like writing code that says Enter car; Make sure you have entered exactly one car;. LIMIT can be used in other cases like retrieving the first 10 results of many.

edited May 23 '17 at 11:56

Community

1
1

answered Jan 28 '14 at 12:55

SebastianH

2,172
1
18
29

You should also mention to move away from the deprecated `mysql_*` and adopt `mysqli`, or preferably `pdo` – ʰᵈˑ Jan 28 '14 at 13:01
I believe it's some kind of login system, so using ID is not an option here. – Eternal1 Jan 28 '14 at 13:02
I just posted a basic example, so I was hoping for a general answer, not a specific one. That being said: @SebastianH - I know how to make sure to be only one field with the mentioned value, the question is how to retrieve it more clean and fast. Also, I am used with mysql, but I know mysqli is the one I will want to use. Thanks for the reminder. – MaximatoR Jan 28 '14 at 13:29
if you are concerned with performance (which you should not except for cases where this is really crucial) then - again from a database point of view - a key is important. I guess I dont have to lecture on data retrieval from a column with no key at all -> full table scan. @Андрей Почекуев In that case it would be crucial to have a unique username which amounts to the same thing. – SebastianH Jan 28 '14 at 15:01

score 0 · Answer 2 · answered Jan 28 '14 at 12:53

you should use the PDO methods if you want any security, regular mysql calls have been fased out for a while

Use mysqli (http://ee1.php.net/mysqli), but check here http://www.php.net/manual/en/book.pdo.php

Your query though would be SELECT status FROM users WHERE username='user1' LIMIT 1

score 0 · Answer 3 · answered Jan 28 '14 at 12:59

First one - you don't use mysql_ methods anymore - they're deprecated and natively unsafe. Second - you use PDO interface, like this

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
    $stmt = $dbh->prepare("SELECT `status` FROM `users` WHERE `username` = ?");
    $stmt->execute(array($_GET['username']));
    $status = $stmt->fetchColumn();

} catch (PDOException $e) {
    echo 'PDO Error : ' . $e->getMessage();
}

Using prepare - execute chain is safe with PDO - it is automatically sanitized, so you don't have to worry about mysql injections. At prepare part you create a query with parameter, and in execute part you execute your prepared query with parameter equaling $_GET and store the result. Any error you encounter along the way is being caught in the catch (PDOException $e) block and can be handled appropriately.

Which is the most secure and correct solution to display only one value from a single row using SELECT in PHP?

3 Answers3