how to avoid quotes in a mysql_query when adding $_POST variables into the query

marcnyc

Hello, I am trying to clean up my code and eliminate some extra code.
What I have is this:

$id = $_POST['id'];
$date = $_POST['date'];
	mysql_query("INSERT INTO `BILLING` (id, date) VALUES ('$id', ' $date')") or die(mysql_error());

In this example I only added $id and $date but that mysql table has about 100 columns and therefore this mysql query is very long and there are about 100 lines like the first two...

In other words the first two lines (and all the others in the real life script) are the ones I am trying to get rid of but I can't figure out how to put the $_POST variables inside the query.

Obviously the following does not work:

	mysql_query("INSERT INTO `BILLING` (id, date) VALUES ('$_POST['id']', '$_POST['date']')") or die(mysql_error());

I tried several combinations of escaping quotes and several combinations of quotes (single, double etc) but I can't seem to find a way to write everything in one line.

Can somebody help me figure it out? Or must I have those 100 $var = $_POST['var'] lines before the query line?

Thanks

rsmith

I would write the query like this:

mysql_query("INSERT INTO BILLING SET 
										id= '".$_POST['id']."',
										id= '".$_POST['date']."') or die(mysql_error());

Try that.

Horizon88

First, that would cause an error, as you can see from the board's syntax highlighting. You've got mismatched single and double quotes. Secondly, you don't escape those variables, which could cause security issues out the nose. 😉

mysql_query('INSERT INTO BILLING SET id = '.mysql_real_escape_string($_POST['id']).';') or die(mysql_error());

That would make the query:

INSERT INTO BILLING SET id = (escaped)$_POST[id];

You could also escape a quote in it as well, to allow for spaces:

mysql_query('INSERT INTO BILLING SET id = \''.mysql_real_escape_string($_POST['id']).'\';') or die(mysql_error());

Which makes the query:

INSERT INTO BILLING SET id = '(escaped)$_POST[id]';

bradgrafelman

Horizon88 wrote:
You could also escape a quote in it as well, to allow for spaces

But if 'id' is a numeric column (which it sounds like it should be), then you shouldn't have quotes around it.

Anyway, the moral of the story is: You should never place user-supplied data directly into a query - always escape it first with [man]mysql_real_escape_string/man (or use other techniques to ensure the data is of the correct type).

marcnyc

Thank you for your suggestions

laserlight

Remember to mark this thread as resolved (if it is).