Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 7 down vote favorite
3

I'm trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type inside VALUES then this doesn't work. What am I doing wrong?

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");
share|improve this question

6 Answers

up vote 9 down vote accepted

The rules of adding strings into a query are plain and simple:

  1. The string should be enclosed in quotes.
  2. Therefore, these quotes should be escaped in the data, as well as some other characters, using mysql_real_escape_string()

So, your code becomes

$type     = 'testing';
$type     = mysql_real_escape_string($type);
$reporter = "John O'Hara";
$reporter = mysql_real_escape_string($reporter);

$query    = "INSERT INTO contents (type, reporter, description) 
             VALUES('$type', '$reporter', 'whatever')";
mysql_query($query) or trigger_error(mysql_error()." in ".$query);
// note that when running mysql_query you have to always check for errors

But if you're going to add another part of query, the rules changes.

  • To add a number, you have to cast it to its type implicitly.

For example:

$limit = intval($_GET['limit']); //casting to int type!
$query = "SELECT * FROM table LIMT $limit";
  • To add an identifier, it's better to choose it from some sort of white list, consists of hardcoded values

For example:

if ($_GET['sortorder'] == 'name') {
  $sortorder = 'name';
} else {
  $sortorder = 'id';
}
$query = "SELECT * FROM table ORDER BY $sortorder";

To make it all simplified yet with guaranteed safety, one have to use some sort of placeholder system where the variable goes into a query not directly but via some proxy, called a placeholder.

So, your query call become something like this:

$type     = 'testing';
$reporter = "John O'Hara";
pquery("INSERT INTO contents (type, reporter, description) VALUES(?s, ?s, ?s)",
        $type, $reporter,'whatever');

And there will be absolutely no need to worry about all these matters.

share|improve this answer
This is very informative. Thanks for the taking the time. – Pinkie Sep 24 '11 at 8:07
2  
I ma glad you valued my answer. It rarely occurs here. Thank you. – Your Common Sense Sep 24 '11 at 8:23
What is pquery()? – Starx Apr 1 '12 at 10:27
@Starx that's just a fictional function to show the idea. I know no library providing such a feature though. – Your Common Sense Apr 1 '12 at 11:24
up vote 4 down vote

As long as it is a string - you have to put it within quotes

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) VALUES('$type', 'john', 'whatever')");

And, yes, as Dani adviced: you should sanitize every string you put in the query with mysql_real_escape_string()

share|improve this answer
3  
You should warn about the SQL injection hazard your code has. – Dani Sep 24 '11 at 6:18
up vote 1 down vote

Try this:

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) VALUES('$type', 'john', 'whatever')");

You need to put '$type' not just $type

share|improve this answer
up vote 1 down vote

If the variables contain user input or other data you can't trust, be sure to escape the data. Like this:

$query = sprintf("INSERT INTO contents (type) VALUES ('%s')", mysql_real_escape_string($type));
$result = mysql_query($query);
share|improve this answer
up vote 1 down vote

Here

$type='testing' //it's string

mysql_query("INSERT INTO contents (type, reporter, description) VALUES('$type', 'john', 'whatever')");//at that time u can use it(for string)


$type=12 //it's integer
mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");//at that time u can use $type
share|improve this answer
up vote 1 down vote

The text inside $type is substituted directly into the insert string, therefore MySQL gets this:

... VALUES(testing, 'john', 'whatever')

Notice that there are no quotes around testing, you need to put these in like so:

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) VALUES('$type', 'john', 'whatever')");

I also recommend you read up on SQL injection, as this sort of parameter passing is prone to hacking attempts if you do not sanitize the data being used:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.