mysql help

[stanks]

I am trying to send this query:

Code Select Expand


SELECT username, password, name1, name2 FROM smthg WHERE name1='%s' AND password=%ld

wsprintf formats this. Real query looks like:

Code Select Expand


SELECT username, password, name1, name2 FROM smthg WHERE name1='stanks' AND password=123456

Everytime i send this query i receive SQL error 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
I read something about mysql_real_escape_string function, but i don't understand what i have to do. Doc says:

Code Select Expand


8.1.1. Strings
A string is a sequence of bytes or characters, enclosed within either single quote ("'") or double quote (""") characters.
Examples:

'a string'
"another string"

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only within single quotes because a string
quoted within double quotes is interpreted as an identifier.

A binary string is a string of bytes that has no character set or collation. A non-binary string is a string of characters that
has a character set and collation. For both types of strings, comparisons are based on the numeric values of the string unit.
For binary strings, the unit is the byte. For non-binary strings the unit is the character and some character sets allow
multi-byte characters. Character value ordering is a function of the string collation.

String literals may have an optional character set introducer and COLLATE clause:

[_charset_name]'string' [COLLATE collation_name]

Examples:

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

You can use N'literal' (or n'literal') to create a string in the national character set. These statements are equivalent:

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

For more information about these forms of string syntax, see Section 9.1.3.5, "Character String Literal Character
Set and Collation", and Section 9.1.3.6, "National Character Set".

Within a string, certain sequences have special meaning. Each of these sequences begins with a backslash ("\"), known
as the escape character. MySQL recognizes the following escape sequences:

\0  An ASCII 0 (NUL) character.
\'  A single quote ("'") character.
\"  A double quote (""") character.
\b  A backspace character.
\n  A newline (linefeed) character.
\r  A carriage return character.
\t  A tab character.
\Z  ASCII 26 (Control-Z). See note following the table.
\\  A backslash ("\") character.
\%  A "%" character. See note following the table.
\_  A "_" character. See note following the table.

For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not
escaped. For example, "\x" is just "x".

These sequences are case sensitive. For example, "\b" is interpreted as a backspace, but "\B" is interpreted as "B".

The ASCII 26 character can be encoded as "\Z" to enable you to work around the problem that ASCII 26 stands
for END-OF-FILE on Windows. ASCII 26 within a file causes problems if you try to use mysql db_name < file_name.

Escape processing is done according to the character set indicated by the character_set_connection system variable.
This is true even for strings that are preceded by an introducer that indicates a different character set, as discussed
in Section 9.1.3.5, "Character String Literal Character Set and Collation".

The "\%" and "\_" sequences are used to search for literal instances of "%" and "_" in pattern-matching contexts where
they would otherwise be interpreted as wildcard characters. See the description of the LIKE operator in Section 11.4.1,
"String Comparison Functions". If you use "\%" or "\_" in non-pattern-matching contexts, they evaluate to the strings
"\%" and "\_", not to "%" and "_".

There are several ways to include quote characters within a string:

A "'" inside a string quoted with "'" may be written as "''".

A """ inside a string quoted with """ may be written as """".

Precede the quote character by an escape character ("\").

A "'" inside a string quoted with """ needs no special treatment and need not be doubled or escaped. In the same way,
""" inside a string quoted with "'" needs no special treatment.

The following SELECT statements demonstrate how quoting and escaping work:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+

If you want to insert binary data into a string column (such as a BLOB column), the following characters must be
represented by escape sequences:

NUL NUL byte (ASCII 0). Represent this character by "\0" (a backslash followed by an ASCII "0" character).
\ Backslash (ASCII 92). Represent this character by "\\".
' Single quote (ASCII 39). Represent this character by "\'".
" Double quote (ASCII 34). Represent this character by "\"".

When writing application programs, any string that might contain any of these special characters must be properly
escaped before the string is used as a data value in an SQL statement that is sent to the MySQL server.
You can do this in two ways:

Process the string with a function that escapes the special characters. In a C program, you can use the
mysql_real_escape_string() C API function to escape characters. See Section 25.2.3.53, "mysql_real_escape_string()".
The Perl DBI interface provides a quote method to convert special characters to the proper escape sequences.
See Section 25.4, "MySQL Perl API". Other language interfaces may provide a similar capability.

As an alternative to explicitly escaping special characters, many MySQL APIs provide a placeholder capability that
enables you to insert special markers into a statement string, and then bind data values to them when you issue
the statement. In this case, the API takes care of escaping special characters in the values for you.


Can somebody explain this to me because i don't understand what i have to do. I tried with:

Code Select Expand


invoke szLen,addr strUName
invoke mysql_real_escape_string,conn,addr strunametemp,addr strUName,eax

and then i query again, but again same error msg :(

Any help?

Thanks

[jj2007]

name and password are too simple, they do not look like candidates for escaping. Try all combinations: name & password without quotes, with quotes etc...

[stanks]

Already try it, but same msg :( Under admin gui this query works fine. Under mysql cmd works fine too. username is varchar type, and password is integer. I will check again my code. I don't know... :(

[stanks]

OK some news. If i send query statement without formating (SELECT * FROM TABLE) everything works ok. But when i use temp. buffer for storing formated query i get that error msg.
This is my buffer.

Code Select Expand


.data
strQuery1 byte "SELECT * FROM table",0
strQuery2 byte "SELECT * FROM table WHERE uname='%s'",0

.data?
TempStatementBuffer byte 512 dup(?)

If i send strQuery1 everything is ok. When i format strQuery2 to get SELECT * FROM table WHERE uname='stanks' and send it, i receive error msg. I tried with szCat and szMultiCat, but same error. Is it just me or this is a bug?

Thanks

[stanks]

I have found the problem  :U The problem is in reading docs...so it is problem on my side  :8) mysql_real_query needs as 3rd param size in bytes of query, not size of buffer  :bg

Thanks

[Draakie]

Hi Stanks - please be so kind as to read your PM.

Draakie