Explain the difference between these 3 queries and which is the best to use?

Question

I searched a lot but unable to find any question which is related to my problem so I posted this one.

I came to know that this following 3 line do the same work.

$q="insert into employee values('".$e."','".$nm."','".$desg."','".$sal."')";

$q="insert into employee values('$e','$nm','$desg','$sal')";

$q="insert into employee values('{$e}','{$nm}','{$desg}','{$sal}')";

$e, $name, &desg, &sal are variables.

I'm confused which one is best and why these 3 working same. 1st one is totally clear to me that it substitutes the variables with value and creates the query.

But in the 2nd and 3rd, its not clear to me how variables are substituted. That is from where I'm learning they says that if I insert a variable into a echo then it should be enclosed with {} or concatenated.

ex: echo "This is {$name}" / echo "This is ".$name;

So I'm confused.

regarding second one you need to study how '' and "" are different in php — pinkal vansia, Aug 17 '13 at 09:13
The best way is none of the above: please use parameterized queries instead. — Jon, Aug 17 '13 at 09:15
Just to confuse you more, there is at least one more way: [heredoc](http://www.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc) — Salman A, Aug 17 '13 at 09:25

score 1 · Answer 1 · edited May 23 '17 at 10:24

1

There are not the different ways of writing queries, these are merely different ways to write strings in PHP. To clear any confusion, you should go through PHP strings manual and read about all possible ways to create strings. The documentation explains the four possible syntax plus how variables within strings are "parsed".

Before you write queries (safe ones) you must understand how strings work in PHP. You can then go through these answers to find out the proper way of writing queries.

edited May 23 '17 at 10:24

Community

1
1

answered Aug 17 '13 at 09:27

Salman A

262,204
82
430
521

1

@JayadrathaMondal - Salman A makes a good point here. You are comparing three ways of stringing together strings. Most of the comments have focused on the fact that doing this for a PHP database call is unwise, regardless of which of the three you use. But you also need to look at the string side of things :) – Nick Aug 17 '13 at 09:30

Nick · Answer 2 · 2013-08-17T09:26:16.853

As people have started to point out in the comments, none of these methods is advisable or safe.

The problem is SQL injection as explained here.

You want to use PDO. See this tutorial or this reference.

So to connect:

$dsn="mysql:host=127.0.0.1;dbname=myDatabase";
$username="myUsername";
$password="myPassword";
try {
    $DBH=new PDO($dsn,$username,$password);
} catch(PDOException $e) {
    echo $e->getMessage();
}

And a sample insertion:

    $STH = $DBH->prepare("INSERT INTO job (snum, date, length, type, ref) VALUES (:u,:d,:l,:t,:r)");
    $STH->bindParam(':u', $myVariable1);
    $STH->bindParam(':d', $myVariable2); 
    $STH->bindParam(':l', $myVariable3);
    $STH->bindParam(':t', $myVariable4);
    $STH->bindParam(':r', $myVariable5);
    try { 
        $STH->execute();
    } catch(PDOException $e) {
        echo $e->getMessage();
    }

score 0 · Answer 3 · answered Aug 17 '13 at 09:26

To answer your question:

This is simple string concatenation:

$q="insert into employee values('" . $e . "','" . $nm . "','" . $desg . "','" . $sal . "')";

This is value substitution, which PHP will do with string literals that use " ":

$q="insert into employee values('$e','$nm','$desg','$sal')";

The third sample is not correct. { and } are only necessary when you want to use the substitution from #2 with array values:

$q="insert into employee values('{$e[0]}','{$nm[0]}','{$desg['somekey']}','{$sal[o]}')";

As mentioned repeatedly, you seriously do not want to be using any of these to build a query string. Both PDO and the MySQLi library have parameterizing functions that are much safer.

Rahul · Answer 4 · 2013-08-17T09:57:41.230

On comparison of these four queries we got:

echo "My name is $name"  //0.00099992752075195

echo "My name is ".$name;  //0.00099992752075195

echo "My name is {$name}"; //0.0010001659393311 

echo "My name is '{$name}'";  //0.0010001659393311

which proves first query will perform better.

these three are good in general use but are most vulnerable to sql injection better use:

use Prepared statements.

eg :

<?php

$q = 'Insert INTO counter(num) values (?); $stmt = mysqli_prepare($dbc, 'i'); // check weather only integer is passed

mysqli_stmt_bind_param($stmt, 'i', $n);

for($n=1;$n<= 100; $n++) { mysqli_stmt_execute($stmt); } ?> `
Use PDO objects in php.

Explain the difference between these 3 queries and which is the best to use?

4 Answers4