CODEWITHSUNDEEP
phpmysqlsql
Nick
Nick

Reputation: 639

What is the correct way to insert a variable for an sql query

i have here a snippet of php that i used to pull certain dates inbetween the two date that the user supplied

if($start_date!="" && $end_date!=""){
$query[]="submit_time BETWEEN '$start_date' and '$end_date'";
}

Why is it that after the string is processed, that works. however it seems that the widley excepted way to do this is:

if($start_date!="" && $end_date!=""){
$query[]="submit_time BETWEEN '".$start_date."' and '".$end_date."'";
}

can any one elaborate why both ways work and which one is the best? i was told today i should tdo it the first way, but i have ALWAYS seperated the variable from the string. any ideas?

or perhaps a better example:

$sql="SELECT * FROM $tbl_name WHERE submit_time BETWEEN 
'$start_date' and '$end_date'";

V.S.

$sql="SELECT * FROM $tbl_name WHERE submit_time BETWEEN 
'".$start_date."' and '".$end_date."'";

Upvotes: 0

Views: 71

Answers (4)

dm03514
dm03514

Reputation: 55972

Neighter is the corect way. If that is the only checking you do you are in for sql injection.

You should be using prepared statements:

http://php.net/manual/en/pdo.prepare.php

if you are just talking about string interpolation in general, I much prefer sprintf as I think it is way more readable and it does type conversions:

$name = 'John Doe';
sprintf("Hello %s - How are you today", $name);

Please under no circumstance use user provided start and end dates in your sql.

Upvotes: 4

Kyle
Kyle

Reputation: 1733

There is literally no difference between them, it's just personal preferance. You really should be using prepared statements (PDO), but at the very least wrap your variables in mysql_real_escape_string() to make it more secure. PDO is the way to go though.

$start_date = mysql_real_escape_string($start_date);
$end_date = mysql_real_escape_string($end_date);

$sql="SELECT * FROM $tbl_name WHERE submit_time BETWEEN 
'$start_date' and '$end_date'";

Upvotes: 0

Till Helge
Till Helge

Reputation: 9311

Actually...none of those versions is really the best solution. It would be a lot better, if you used a prepared statement and supplied the start and end date as parameters for this query. If you use PDO, it would look something like this:

$stmt = $db->prepare('SELECT * FROM somewhere WHERE submit_time BETWEEN :startdate AND :enddate');
$stmt->bindParam(':startdate', $start_date);
$stmt->bindParam(':enddate', $end_date);
$stmt->execute();

This way you can be sure there will be no messing with your query and it also will be executed faster, as the query parser doesn't have to evaluate whether your variables contain any SQL syntax.

To answer the rest of your question: It's a matter of writing clean code. Embedding variables in a string looks sort of messy as you can easily tell in any editor that provides syntax highlighting. It becomes especially messy if you have array elements to be added to your string.

Upvotes: 3

anon
anon

Reputation:

I have always used the first method, I think it just comes down to as a matter of choice, than having anything to do with your codding. BUT, that is just my opinion.. let's wait and see others

Upvotes: 0

Related Questions