MySQL select from db efficiency and time-consumption

Question

Which one of those will take the least amount of time?

1.

q = 'SELECT * FROM table;'
result = db->query(q)
while(row = mysql_fetch_assoc(result))
{
userName = row['userName'];
userPassword = row['userPassword'];
if (enteredN==userName && enteredP==userPassword)
return true;
return false;
}

2.

q = 'select * from table where userName='.enteredN.' and userPassword='.enteredP.';'
...

Answer 1

The second one is guaranteed to be significantly faster on just about any database management system. You may not notice a difference if you have just a few rows in the table, but when you get to have thousands of rows, it will become quite obvious.

As a general rule, you should let the database management system handle filtering, grouping, and sorting that you need; database management systems are designed to do those things and are generally highly optimized.

If this is a frequently used query, make sure you use an index on the unique username field.

As soulmerge brings up, you do need to be careful with SQL injection; see the PHP Manual for information how to protect against it. While you're at it, you should seriously consider storing password hashes, not the passwords themselves; the MySQL Manual has information on various functions that can help you do this.

Answer 2

Number 2 is bound to be much, much!, faster. (unless the table only contains a few rows)

It is not only be SQL servers are very efficient at filtering (and they can use indexes etc. which the loop in #1 doesn't have access to), but also because #1 may cause a very significant amount of data be transferred from the server to the php application.

Furthermore solution #1 would cause all the login credentials of the users to transit on the wire, exposing them to possible snooping, somewhere along the line... Do note that solution #2 also incur a potential security risk if the channel between SQL server and application is not secure; this risk is somewhat lessened because 100% of the accounts details are not transmitted with every single login attempt.

Beyond this risk (which speaks to internal security), there is also the very real risk of SQL injection from fully outside attackers (as pointed out by others in this post). This however can be fully addressed by escaping the single quote characters contained within end-users supplied strings.