Mysqli queries, php queries,and quotes...single quotes, doubles

PHP
1

I;m confused about the rules for quotes. In a book I’m reading,

a query looks like this
$isbn = $_POST[‘isbn’];
$author=$_POST[‘author’];
$title = $_POST[‘title’];
$price=$_POST[‘price’];

$query = “insert into books values('”.$isbn.“', '”.$author.“', '”.$title.“', '”.$price.“')”;

So this inserts the POST values into the database. I’ve tried looking at the official php documentation but I haven’t been able to find exactly what I’m looking for. What are the rules for single quotes and double quotes when it comes to variables? And are the periods just concentation operators or in this context do they mean something else?

2

What book are you reading? If you are using mysqli (not mysql) in PHP you should really look at the prepared queries syntax as it will sanitize your input for you ensuring you don’t have unescaped single/double quotes that would otherwise break your query via concatenation.

3

Simply assigning the $_POST fields to variables is pointless. The $_POST fields are already variables.

What you should be doing when you move the values out of $_POST is to validate them to ensure that they do not contain characters that are invalid for that particular field to contain. That way you can then distinguish between the $_POST variables that might contain invalid data and the other variables which you know are valid.

Then you do as cpradio suggested and use prepare and bind statements to insert the data into the database in a way that keeps the data separate from the SQL and so allows valid quotes in the data to be processed correctly.

4

I’m reading PHP and MYSQL web development by Luke Welling and Laura Thompson.

5

. by itself is the string concatenate operator . http://us1.php.net/manual/en/language.types.string.php talks about different quotes.

If the book has:


$query = "insert into books values('".$isbn."', '".$author."', '".$title."', '".$price."')";

for a pdo example then toss the book into the garbage or recycle it. It’s doing you more harm then good.

The example should look something like:


$sql = 'insert into books values(:isbn,:author,:title,:price)';
$params = array('isbn' => $_POST['isbn'],'author' => $_POST['author'],'title' => $_POST['title'],'price' => $_POST['price']);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
6

No it shouldn’t - you should never use tainted variables such as $_POST as input to anything other than validation or sanitization.

7

I always liked the movie “Never Say Never”.

In this case, the $_POST arguments ARE being passed to both validation and sanitation routines.

Sanitization(sic) because using prepared statements takes care of escaping,quoting and all the nasty sql injection stuff.

Validation because your database will complain if you violate rules such as no title.

Of course you could do more validation and catch problems before they hit the database but it’s not required.