I am always unsure, and I find myself playing around with it a lot.
Is this proper?
$game_name = "poker"; (Included from Config.php)
$game_name_lower = strtolower($game_name);
$sql = "SELECT * FROM `winners` WHERE `game` = '$game_name_lower'";
views:
60answers:
5
+1
A:
It is impossible to say without seeing the code around it. If $game_name_lower comes directly from user input then it certainly is not. Otherwise the quoting is fine, if unnecessary in all cases.
Ignacio Vazquez-Abrams
2010-08-18 09:13:30
+2
A:
Yes, looks fine. However, you may want to consider using prepared statements instead. Quoting the PHP Manual on the topic:
Prepared statements offer two major benefits:
The query only needs to be parsed (or prepared) once, but can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize it's plan for executing the query. For complex queries this process can take up enough time that it will noticeably slow down an application if there is a need to repeat the same query many times with different parameters. By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle. This means that prepared statements use fewer resources and thus run faster.
The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).
Daniel Vassallo
2010-08-18 09:15:11
+1 Prepared statements
Mark Baker
2010-08-18 09:16:31
actually prepared statements has nothing to do with user input. actually it prevents unexpected behavior caused by malformed data. and should be used with any data, not user input only
Col. Shrapnel
2010-08-18 09:19:35
@Col: Yes, user input can attract some nasty "malformed data". BTW: It wasn't my intention to say that prepared statements should be used only with user data... Does it look like I'm saying that?
Daniel Vassallo
2010-08-18 09:21:10
Yes it does. More of that - because of such phrasing, every newbie believes that only input should be escaped/prepared. And thinks of data preparations in terms of user input, not SQL query. foreach over $_POST is the only idea they have. Don't you see it in hunderds of questions here?
Col. Shrapnel
2010-08-18 09:27:52
and that `especially` word is still wrong and misleading. What is actual difference between user input and, say, other query result?
Col. Shrapnel
2010-08-18 09:30:05
@Col: Ok, that could be true. Updated my answer, citing an authoritative source, quoting the php manual.
Daniel Vassallo
2010-08-18 09:36:05
In my opinion, the only advantage of prepared statements - it's ease of use. One don't have to think how to treat source data - to quote or to cast or anything, but just bind in every case. As for the speed, there is very little use of it, and as of it's safeness, it is as safe, as properly used quiting/casting.
Col. Shrapnel
2010-08-18 11:53:57
A:
Should be something like this you don't need the ' around the table name or column name.
$dbc = mysql connection;
$q = "SELECT * FROM winners WHERE game = '$game_name_lower'";
$r = @mysqli_query($dbc, $q);
Jonathan Stowell
2010-08-18 09:18:09
It's true that the backticks aren't required in this case, but they cause no harm.
Daniel Vassallo
2010-08-18 09:19:25
+1
A:
Actually here is very little of PHP quotation.
Only surrounding " belongs to PHP.
The rest is mysql thing.
- Backticks are used to delimit identifiers - table and column names
- Single (or double quotes) are used to delimit strings. Due to these delimiters and some other conditions, a string content should be properly escaped. Though there is another way to pass strings (as well as other types of data) to the database - prepared statements, mentioned above.
Col. Shrapnel
2010-08-18 09:25:18