Mysql problem

[milkshake333]

Got this code to insert into a table:

"INSERT INTO houses (ID, en_X, en_Y, en_Z, en_tX, en_tY, en_tZ, ex_X, ex_Y, ex_Z, ex_tX, ex_tY, ex_tZ, int, dim, cost, owner, key) VALUES('" ..ID.. "', '" ..tostring(eX).. "', '" ..tostring(eY).. "', '" ..tostring(eZ).. "', '" ..tostring(etX).. "', '" ..tostring(etY).. "', '" ..tostring(etZ).. "', '" ..tostring(exX).. "', '" ..tostring(exY).. "', '" ..tostring(exZ).. "', '" ..tostring(extX).. "', '" ..tostring(extY).. "', '" ..tostring(extZ).. "', '" ..int.. "', '" ..dim.. "', '" ..cost.. "', 'none', ' ')" 

It has to do something with the syntax of this code, but I can't see what...

The table houses does exists, and all the arguments are passed to the function. It gives the error code 1064, which is ER_PARSE_ERROR.

And it says something like this on the mysql reference website: Message: %s near '%s' at line %d. I don't really understand it....

[dzek (varez)]

before executing do outputDebugString with that long string you gave us - and you will probably see what's wrong.

Your query syntax is incorrect, and it's hard to see becouse it looks so messy - it's much simplier to display query before execution.

And do you know about SQL Injection? Maybe some strings are unescaped, and contains single quote character --> ' <-- which makes query incorrect?

Little eqample (unrelated to your database)

  
name = "My Mansion" 
owner = "varez" 
query = "INSERT INTO houses (name, owner) VALUES ('"..name.."', '"..owner.."')" 
-- after displaying: INSERT INTO houses (name, owner) VALUES ('My Mansion', 'varez') 
-- and it's ok 
-- but this will fail (and it's unsafe!!) 
name = "varez's house" 
owner = "varez" 
query = "INSERT INTO houses (name, owner) VALUES ('"..name.."', '"..owner.."')" 
-- after displaying: INSERT INTO houses (name, owner) VALUES ('varez's house', 'varez') 
-- See that single quote line above? It's breaking the query, making it incorrect. 
-- In this way somebody can inject custom query (like DROP DATABASE!) 
-- Read about MySQL data escaping. 
  

[milkshake333]

Thank you for replying, I will test this. And no, I didn't know about injection. Also I will read about escaping strings. Thanks again

[dzek (varez)]

well, this is extremly important, you dont want your database to be removed by somekind of "hacker"

[milkshake333]

Well I did outputDebugString, and I still can't find the problem...

[dzek (varez)]

can you copy and paste the code it outputted?

There is a problem with syntax for sure

[milkshake333]

INFO: INSERT INTO houses (ID, en_X, en_Y, en_Z, en_tX, en_tY, en_tZ, ex_X, ex_Y, ex_Z, ex_tX, ex_tY, ex_tZ, int, dim, cost, owner, key) VALUES('1', '427.71234130859', '-1573.3515625', '25.80558013916', '427.71234130859', '-1573.3515625', '25.80558013916', '427.71234130859', '-1573.3515625', '25.80558013916', '427.71234130859', '-1573.3515625', '25.80558013916', '3', '2', '100', 'none', ' ')

Dots shouldn't be the problem, 'cause those work normally too.

And I store this whole string in a variable. Then it's something like this:

  
myquery = mysql_query(connection, mystring) 
  

[dzek (varez)]

omg, i didnt see it before.

You are using internal MySQL words as a column names (INT, KEY).

To fix it, add this character - ` (it's something like single quote - on standard keyboards it's on the left from "1" key) before and after such reserved word (of course if it's column name [or table name, if you will ever call your table "int"], for values you should use standard single quote).

Like:

INSERT INTO `houses` (`ID`, `en_X`, `en_Y`, `en_Z`, `en_tX`, `en_tY`, `en_tZ`, `ex_X`, `ex_Y`, `ex_Z`, `ex_tX`, `ex_tY`, `ex_tZ`, `int`, `dim`, `cost`, `owner`, `key`) VALUES('" ..ID.. "', '" ..tostring(eX).. "', '" ..tostring(eY).. "', '" ..tostring(eZ).. "', '" ..tostring(etX).. "', '" ..tostring(etY).. "', '" ..tostring(etZ).. "', '" ..tostring(exX).. "', '" ..tostring(exY).. "', '" ..tostring(exZ).. "', '" ..tostring(extX).. "', '" ..tostring(extY).. "', '" ..tostring(extZ).. "', '" ..int.. "', '" ..dim.. "', '" ..cost.. "', 'none', ' ') 

anyway - its better to aviod naming columns/tables with reserved words.

[milkshake333]

Well, it worked. Thank you very much .