[Jacksta21]

Okay, I've been using MySQL for nearly a month but the syntax still throws me. When a player registers it gives out the following error:

pawn Код:

[ERROR] CMySQLQuery::Execute[] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The query I'm trying to execute...

pawn Код:

mysql_format(MySQL, query, sizeof(query), "INSERT INTO `accounts` (Username, Password, Money, Score, posX, posY, posZ, Admin, Skin, Age, Sex, Interior, VirtualWorld, CellNum, Job, Accent, Faction, Hospital, Insurance, Title, TitleEnabled, Developer, VIP, Credits, Banned, BannedBy, Reason, Ip) \
                VALUES ('%s', %i, %i, %i, %f, %f, %f, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d, %d, '%s', '%s', '%s')",
                name, //Username
                udb_hash(inputtext), //Password
                200, //Cash
                0, //Score
                x, // posX
                y, // posY
                z, // posZ
                0, //Admin check.
                0, //Skin
                23, //Age
                0, //Gender (Male)
                0, //Interior
                0, //VirtualWorld
                0, //Cellnum
                0, //Job
                0, //Accent
                255, //INVALID_FACTION_ID
                0, //Hospital
                0, //Insurance
                blankstring, //Blank title
                0, //Title Enabled
                0, //Developer Status
                0, //VIP Status
                0, //Credits
                0, //Banned is 0.. Obviously
                blankstring, //BannedBy
                blankstring, //reason
                blankstring //IP, blank because it will be assigned when player logs in.
                );

I know it's messy... It worked up until now.
Any help is appreciated

[Misiur]

How big is your query variable? Syntax is good, number of arguments matches, SQLi exploitation is probably not present (%e is your friend), and I think you didn't trip on any reserved keyword. What does your mysql_log say about the query?

[Jacksta21]

Quote:

Originally Posted by Misiur How big is your query variable?

Have a feeling that could be the issue although theres no mention in the mysql_log.
As soon as I saw that I bumped up the query variable to 500 cells. Testing now..
EDIT: That was the issue, thanks for that! My idiocity probably wouldn't have found that alone...

[BroZeus]

there should be ' ' around every format specifier like %i %d %f not in %s only in all

[Patrick]

Quote:

Originally Posted by BroZeus there should be ' ' around every format specifier like %i %d %f not in %s only in all

No ' '(Apostophe) are used correctly, Apostophe are meant to be used only on string not on floats or integer. Also instead of putting 0 in arguements you could directly put zero in the query. something like this.

pawn Код:

mysql_format(szQuery, sizeof(szQuery), "INSERT INTO Tables (PlayerID, PlayerScore, PlayerMoney) VALUES(0, 0, 0) "

Complete

pawn Код:

mysql_format(MySQL, query, sizeof(query), "INSERT INTO `accounts` (Username, Password, Money, Score, posX, posY, posZ, Admin, Skin, Age, Sex, Interior, VirtualWorld, CellNum, Job, Accent, Faction, Hospital, Insurance, Title, TitleEnabled, Developer, VIP, Credits, Banned, BannedBy, Reason, Ip) \
                VALUES ('%e', %i, 200, 0, %f, %f, %f, 0, 0, 23, 0, 0, 0, 0, 0, 0, 255, 0, 0, '%e', 0, 0, 0, 0, 0, '%e', '%e', '%e')",
                name, //Username
                udb_hash(inputtext), //Password
                x, // posX
                y, // posY
                z, // posZ
                blankstring, //Blank title
                blankstring, //BannedBy
                blankstring, //reason
                blankstring //IP, blank because it will be assigned when player logs in.
                );

%e speciefier was added in updated version, not sure which version, but %e automatically escapes your string without using mysql_escape_string afaik.

[BroZeus]

Quote:

Originally Posted by Patrick_ No ' '(Apostophe) are used correctly, Apostophe are meant to be used only on string not on floats or integer. Also instead of putting 0 in arguements you could directly put zero in the query. something like this. pawn Код: mysql_format(szQuery, sizeof(szQuery), "INSERT INTO Tables (PlayerID, PlayerScore, PlayerMoney) VALUES(0, 0, 0) " Complete pawn Код: mysql_format(MySQL, query, sizeof(query), "INSERT INTO `accounts` (Username, Password, Money, Score, posX, posY, posZ, Admin, Skin, Age, Sex, Interior, VirtualWorld, CellNum, Job, Accent, Faction, Hospital, Insurance, Title, TitleEnabled, Developer, VIP, Credits, Banned, BannedBy, Reason, Ip) \                 VALUES ('%s', %i, 200, 0, %f, %f, %f, 0, 0, 23, 0, 0, 0, 0, 0, 0, 255, 0, 0, '%s', 0, 0, 0, 0, 0, '%s', '%s', '%s')",                 name, //Username                 udb_hash(inputtext), //Password                 x, // posX                 y, // posY                 z, // posZ                 blankstring, //Blank title                 blankstring, //BannedBy                 blankstring, //reason                 blankstring //IP, blank because it will be assigned when player logs in.                 );

i am not good at mysql but just opened a gamemode of mysql and in that there is ' ' around every specifier in query why is that
and it works pretty good to with it

[Misiur]

@OP:
I'm not sure if mysql_format supports this, but use %.2f for floats. Why? Normally they take up ~8 chars (0.000000 instead 0.00). And you shouldn't need more precision than 3. Also I still encourage using %e instead of %s. Oh, and simply print your query after mysql_format, then check it out in server log.

[iZN]

Quote:

Originally Posted by BroZeus i am not good at mysql but just opened a gamemode of mysql and in that there is ' ' around every specifier in query why is that and it works pretty good to with it

They're not necessary, but you've to add those apostrophe outside the strings no matter what.

Quote:

Originally Posted by Misiur @OP: I'm not sure if mysql_format supports this, but use %.2f for floats. Why? Normally they take up ~8 chars (0.000000 instead 0.00). And you shouldn't need more precision than 3. Also I still encourage using %e instead of %s. Oh, and simply print your query after mysql_format, then check it out in server log.

He found the issue already.

Quote:

Originally Posted by Jacksta21 EDIT: That was the issue, thanks for that! My idiocity probably wouldn't have found that alone...

[Vince]

Quote:

Originally Posted by BroZeus i am not good at mysql but just opened a gamemode of mysql and in that there is ' ' around every specifier in query why is that and it works pretty good to with it

It works, because the MySQL server does an implicit type conversion. Still, better to avoid it.