CODEWITHSUNDEEP
phpsql
BloonsTowerDefence
BloonsTowerDefence

Reputation: 1204

Storing a string in a SQL database

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.

Upvotes: 0

Views: 315

Answers (6)

PeeHaa
PeeHaa

Reputation: 72652

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

Upvotes: 2

Jarrett Barnett
Jarrett Barnett

Reputation: 803

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

Upvotes: 0

Dimensional Sys
Dimensional Sys

Reputation: 498

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.

Upvotes: 0

Michael Aston
Michael Aston

Reputation: 21

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

Upvotes: 0

MatsLindh
MatsLindh

Reputation: 52792

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

Upvotes: 2

Romuloux
Romuloux

Reputation: 1183

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().

Upvotes: 5

Related Questions