0

I am just playing around with .php and SQL, and am trying to test somethings out. I know that storing a variables in a table is a very easy thing to do, but for some reason it is not working for me now.

Here is the table I have: 

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| v1    | varchar(255) | YES  |     | NULL    |       | 
| v2    | varchar(255) | YES  |     | NULL    |       | 
| v3    | varchar(255) | YES  |     | NULL    |       | 
| v4    | varchar(255) | YES  |     | NULL    |       | 
| v5    | varchar(255) | YES  |     | NULL    |       | 
| v6    | varchar(255) | YES  |     | NULL    |       | 
| v7    | varchar(255) | YES  |     | NULL    |       | 
| v8    | varchar(255) | YES  |     | NULL    |       | 
| v9    | varchar(255) | YES  |     | NULL    |       | 
| v10   | varchar(255) | YES  |     | NULL    |       | 
+-------+--------------+------+-----+---------+-------+

I access the table like so:

$result = mysql_query("INSERT INTO form2 (v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES ($field0, $field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8, $field9)");

I get the variables from the url by doing this:

$field0 = $_GET['field0'];
$field1 = $_GET['field1'];
$field2 = $_GET['field2']; 
$field3 = $_GET['field3'];
$field4 = $_GET['field4'];
$field5 = $_GET['field5'];
$field6 = $_GET['field6'];
$field7 = $_GET['field7']; 
$field8 = $_GET['field8'];
$field9 = $_GET['field9'];

And lastly, my URL is:

http://mywebsite.ca/anapplication?field0=YES&field1=GOOD&field2=GOOD&field3=GOOD&field4=YES&field5=GOOD&field6=GOOD&field7=GOOD&field8=A&field9=&

I get an error message saying that there is an unknown column 'YES' (the first parameter). I passed the same URL with number instead of strings and to my surprise everything worked then.

I don't have much experience with .php or SQL, so I am looking for a fresh set of eyes to take a quick look through what I've posted here.

Improve this question
2

6 Answers 6

Reset to default
5

I believe you need to wrap your values in single quotes, that way mysql treats them as strings instead of integers. This would be why passing only numbers through works while strings do not.

So,

$result = mysql_query("INSERT INTO form2 (v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES ('$field0', '$field1', '$field2', '$field3', '$field4', '$field5', '$field6', '$field7', '$field8', '$field9')");

should clear it up.

Also, if this is coming from a web form or other outside source, make sure to sanitize it using something like mysql_real_escape_string().

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

No Problem, done it before and was scratching my head for 20 minutes.
2

You should probably have a look at mysqli or PDO instead of the old mysql_ library, but other than that, the issue is that you're not generating valid SQL.

INSERT INTO form2 (v1, v2 ...) VALUES(GOOD0, GOOD1 ...)

is not valid SQL: you'll have to add single or double quotes around the values for them to be treated as strings:

INSERT INTO form2 (v1, v2 ...) VALUES('GOOD0', 'GOOD1' ...)

Since the strings submitted can also contain quotes (and therefore make your generated SQL invalid or perform tasks you don't want it to perform), you should always use prepared statements or if not available, escape the string properly by using the library's escape function (such as mysql_real_escape_string).

Improve this answer

Comments

2

As I already commented:

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is a good PDO tutorial.

When using prepared statements you also get rid of that nasty SQL Injection vulnerability you have there.

To use PDO you would do something like:

$pdo= new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO form2 (v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES (:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, v10');
$stmt->execute(array(
    ':v1'  => $_GET['field0'],
    ':v2'  => $_GET['field1'],
    ':v3'  => $_GET['field2'],
    ':v4'  => $_GET['field3'],
    ':v5'  => $_GET['field4'],
    ':v6'  => $_GET['field5'],
    ':v7'  => $_GET['field6'],
    ':v8'  => $_GET['field7'],
    ':v9'  => $_GET['field8'],
    ':v10' => $_GET['field9'],
));

As for your original question: you just forgot the quotes around the values :-)

Improve this answer

Comments

0

It looks like you are actually trying to input the values that those variables represent as opposed to actually inserting $field0 into the v1 field. So you would need to concatenate the string with the single quotes. It's been a while since I used PHP, but that should be using the "." if I remember correctly so it might be something like:

$result = mysql_query("INSERT INTO form2 (v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES ('" . $field0 . "', '" . $field1 . "', '" . $field2 . "', '" . $field3 . "', '" . $field4 . "', '" . $field5 . "', '" . $field6 . "', '" . $field7 . "', '" . $field8 . "', '" . $field9 .  "')");
Improve this answer

Comments

0

ok, in PHP we do not have debugger mode to watch variables and steps that are executing program so whenever you find such types of strange errors or bugs in PHP, do not insert directly values in database in beginner's level, first you should get all the values from query string as you mention in your question, then create a query and assign into a variable like

$query ="INSERT INTO form2 (v1, v2, v3, v4, v5, v6, v7, v8, v9, v10) VALUES ($field0, $field1, $field2, $field3, $field4, $field5, $field6, $field7, $field8, $field9)";

and then you need to echo that query echo $query; on the browser, execute first it on SQLYog or PHPMyAdmin, if it works fine then do the remaining work in PHP coding. I think it is a very nice practice for starters.

Thank you.

Improve this answer

2 Comments

There are several proper debuggers and frontends available for PHP, so if you want to run things under a debugger, have a look at xdebug , Gubed or DBG.
You should never ever be inserting values directly in the database at any level :-) when using the ancient mysql_* functions.
0

My guess is that somehow PHP is assuming the "YES" is a boolean (like TRUE or FALSE or 0 and 1 where 0 equals NO and 1 equals YES).

First method would be to not use the full word "YES" but rather "y" or "n". I notice many databases use a CHAR(1) column for yes or no type data... which stores a simply "y" or "n" in the field.

If that's not an option, the best way to guarantee that your inserting a string is to have PHP convert it to a string before the SQL INSERT.

Unfortunately PHP does not make converting BOOLEANS into STRINGS as simple as strval(). But what you CAN do is check whether the value is TRUE or FALSE and set the value accordingly.

<?php
$field0 = ($_GET['field0'] == "YES") : "YES" : "NO"; // if equals to YES set to (string) YES; else set to (string) NO.

http://php.net/manual/en/language.types.boolean.php

Improve this answer

1 Comment

This is unrelated to the question, but PHP will not assume "YES" is an actual boolean value unless you attempt to use it as a boolean. The only reason it evaluates as true is that it's a string with actual content.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.