mysqli bind_param vs '".$var."'; whats the difference?

Question

I'm trying to figure out the difference between using prepared statements and and escaping/converting a variable into a string as follows:

$sql = "SELECT `user_id`, `user_name` FROM table WHERE `user_id` = ? and `user_name`= ?";
$sqlPrepare = $conn->prepare($sql);
$sqlPrepare->bind_param('ss', $user_id, $user_name);
$sqlPrepare->execute();
$result = $ $sqlPrepare->get_result();
            if($result->num_rows ===0)
                         {
                        // Do work
                         }

VS

mysqli::real_escape_string($whatever_vars_needed);

$sql = "SELECT `user_id`, `user_name` FROM table WHERE `user_id` = '".$user_id."' and `user_name`= '".$user_name."'";
$sqlQuery = $conn->query($sql);
    if($sqlQuery->num_rows ===0)
                         {
                        // Do work
                         }

as far as protecting against sql injections go, would they both serve the same purpose? And if so, wouldn't it be preferred to use the second method since it does save a bit of extra typing?

I realize that I'm using query vs prepare but even then I don't really see the difference if I'm converting the variables into strings?

Which is a better method?

The first one doesn't even mix the variables and the query. It sends them separately to the database engine. — Blender, Oct 06 '13 at 20:49
Read [this](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496) — , Oct 06 '13 at 20:49
Read rather [this](http://stackoverflow.com/a/8265319/285587) — Your Common Sense, Oct 06 '13 at 20:51
see this question i think it is similair http://stackoverflow.com/questions/18596803/avoiding-an-sql-injection-attack — Lamloumi Afif, Oct 06 '13 at 20:54

score 3 · Answer 1 · answered Oct 06 '13 at 20:57

3

since it does save a bit of extra typing

this is quite interesting phenomenon of PHP subculture.
For some reason, regular PHP user have no idea of user-defined functions or any other complex control or data structure. Therefore, the only their idea of "saving a bit typing" is to get rid of some "unnecessary" operations like safety measures or error reporting.

Browsing through php tag on this site, you may find thousands of short-hands, some of them even quite smart - but for some reason none of them ever involving user defined functions - but raw PHP functions only.

answered Oct 06 '13 at 20:57

Your Common Sense

156,878
40
214
345

2

"For some reason" --- we all know the reason actually: an average php developer uses WP/some-other-crappy-CMS to build sites in 3 days. UDF/SP? whut? – zerkms Oct 06 '13 at 20:58
well the extra typing is saved **if** they both serve the same purpose - as in a redundant feature. Since it does not - then it isn't any longer about saving typing time. For some reason, php developers who condescend tend to miss the actual question. – maestro416 Oct 06 '13 at 21:00
3

@Fahad: actually you should be driven by application design, not by number of keystrokes. Btw, I'm wondered why you put your number inside quotes. It's a 2 additional keystrokes. Doesn't seem to be *optimal*, does it? PS: oh, you even bind it as a string. /me sighs – zerkms Oct 06 '13 at 21:02
Yep. They also fail to use search. – Your Common Sense Oct 06 '13 at 21:02
@zerkms what number are you talking about? there aren't any numbers in the post /reread the post. – maestro416 Oct 06 '13 at 21:06
@Fahad: I see, so `user_id` is a string? – zerkms Oct 06 '13 at 21:07
@zerkms you should be driven by common sense not assumptions. $user_id can = zerkms and user_name can = your_first_name. This is just a place to post a sample - it doesn't imply that the actual application is designed or structured with those specific parameters /lol – maestro416 Oct 06 '13 at 21:09
1

@Fahad: well, anyway - do you understand what prepared statement is? "common sense" --- you won't believe, but 99% of people who ask such a question barely understand difference between a number and a string. So I follow common SO sense. – zerkms Oct 06 '13 at 21:10

mysqli bind_param vs '".$var."'; whats the difference?

1 Answers1