PHP single quotes within the double quote

Question

I am having question about why single quote should be used within the double quote when preparing insert statement. My understanding is variable with double quotes will get interpreted. variable with single quotes will not get interpreted.

$var1 = 5; print "$var1"; //output value 5
$var2 = 'jason'; print '$var2'; //output $var2

I am not sure why the single quotes are used in the values section in the code below. Please give me some explanation. Thanks!

$first_name = $_POST['first_name'];
$first_name = trim($first_name);
$last_name = $_POST['last_name'];

$stmt = $con->prepare("insert into reg_data (first_name, last_name) 
                       values('$first_name', '$last_name')");

Because else MySQL would bug out in case it wasn't a number as you're not parsing the values in `'`'s. It'd be like creating a PHP string like this: `$myvar = mytext`, and it wouldn't work. Please note that you are preparing wrong and are still very vulnerable to SQL injections. — h2ooooooo, Feb 20 '13 at 13:25
Did you search stackoverflow ? http://stackoverflow.com/questions/3446216/difference-between-single-quote-and-double-quote-string-in-php — xblitz, Feb 20 '13 at 13:26
You shouldn't use single quotes there. You should use placeholders and bound variables. As it is, you are mashing user input strings into your SQL and exposing yourself to SQL Injection attacks. — Quentin, Feb 20 '13 at 13:26

score 4 · Accepted Answer · answered Feb 20 '13 at 13:26

4

Your usage of quotes is correct, but you're using prepared statements incorrectly - your code is vulnerable to SQL injection! Instead, use placeholders (without quotes) in the query, and pass in the actual values later, as in the example:

$first_name = $_POST['first_name'];
$first_name = trim($first_name);
$last_name = $_POST['last_name'];

$stmt = $con->prepare("insert into reg_data (first_name, last_name) 
                       values(:first_name, :last_name)");
$stmt->execute(array(':first_name' => $first_name, ':last_name' => $last_name));

answered Feb 20 '13 at 13:26

phihag

278,196
72
453
469

2

That is assuming the $con is a PDO or similar object. – CLo Feb 20 '13 at 13:27
@Chris Since `prepare` is a PDO function name, and the `$stmt` is the generic variable name for PDOStatement objects, I think thatt's safe to assume. – phihag Feb 20 '13 at 13:29
1

You can't assume that, MySQLi also has a `prepare` method that doesn't support placeholder names. I've also seen people build their own database classes that have a `prepare` method which is far from PDO compatible. – h00ligan Feb 20 '13 at 13:33
@h00ligan indeed quite true, plus `$stmt` is a widely used variable name for MySQLi prepared statements. OP needs to clarify if they're using PDO or MySQLi. – MrCode Feb 20 '13 at 13:36
Hello Phihag: Thanks for the reply, I understood how to use the placeholders within a prepare statement. – user2061466 Feb 21 '13 at 03:46

score 2 · Answer 2 · answered Feb 20 '13 at 13:26

2

The single quotes are literally single quotes for the SQL. The SQL becomes:

insert into reg_data (first_name, last_name) values('bob', 'bobowitz')

Literal values in SQL need to be surrounded in single quotes.

answered Feb 20 '13 at 13:26

CLo

3,650
3
26
44

score 1 · Answer 3 · answered Feb 20 '13 at 13:25

1

WHY: Single quotes are used to get final query like:

insert into reg_data (first_name, last_name) values('Bogdan', 'Burim')

Generally bad idea.

SQL injection is possible.

answered Feb 20 '13 at 13:25

Bogdan Burym

5,482
2
27
46

score 0 · Answer 4 · answered Feb 20 '13 at 13:26

The single quotes are delimiters for the SQL statement, and have nothing to do with PHP. They tell MySQL where the value for the field starts, and where it stops.

Consider this:

SELECT * FROM mytable WHERE field=new order by name desc

Should the query restrict where field = "new order by name desc" or should it restrict where field = "new" and then order the results by the "name" field in descending order?

The quotes make that explicit:

SELECT * FROM mytable WHERE field='new order by name desc'

Furthermore: this is the reason you should escape your values in MySQL. If a user types ' OR 1 = 1; -- into your username field, and your query for authentication is:

SELECT * FROM users WHERE username = '$_POST['username']' AND password = '$_POST['password']'

You end up with:

SELECT * FROM users WHERE username = '' OR 1 = 1 -- ' AND password = 'asfd'

Which is valid SQL and will select all users in your table (because the input from $_POST['username'] has made the rest of the query commented out. However, if you properly escape values, you end up with:

SELECT * FROM users WHERE username = '\' OR 1 = 1 -- ' AND password = 'asfd'

Which means MySQL will look for a user with username = \' OR 1 = 1 -- and a matching password. You can escape with a simple mysqli_real_escape_string, however PDO and parameterized queries are always recommended.

I highly recommend you read through some tutorials on PDO, parameters, and SQL injection before working with a database in any web application. You'll thank yourself later.

score 0 · Answer 5 · answered Feb 20 '13 at 13:37

Guys who stated their answers are very technical and may gave very concrete answer how use of single quotes in query will invite SQL injection. But as you asked that why you should use single quote in query I will try to explain you with an example.

Query with static values:

insert into reg_data (first_name,last_name)values('fn_value','ln_value');

you can not remove single quotes from value section as if you'll do that you'll get an error of mysql as the values are strings. so to insert a string value in db with variables you've to put that variables in single quotes and also to avoid mysql error.

PHP single quotes within the double quote

5 Answers5

Linked