MySQL Syntax error - Printable Version

MySQL Syntax error - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL Syntax error (/showthread.php?tid=516484)

MySQL Syntax error - Jacksta21 - 31.05.2014

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

Re: MySQL Syntax error - Misiur - 31.05.2014

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?

Re: MySQL Syntax error - Jacksta21 - 31.05.2014

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

Re: MySQL Syntax error - BroZeus - 31.05.2014

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

Re: MySQL Syntax error - Patrick - 31.05.2014

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.

Re: MySQL Syntax error - BroZeus - 31.05.2014

Quote:

Originally Posted by Patrick_

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

Re: MySQL Syntax error - Misiur - 31.05.2014

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

Re: MySQL Syntax error - iZN - 31.05.2014

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

Re: MySQL Syntax error - Vince - 31.05.2014

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.