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')");

Answer 1

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));

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.