logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
Re: Possible replacement for SQLEncodeSpecialCharacters()
Goto page Previous  1, 2
 
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules
View previous topic :: View next topic  
Author Message
Roland Rosier



Joined: 06 Mar 2005
Posts: 5

PostPosted: Tue Mar 22, 2005 15:18    Post subject: Reply with quote

As far as I can tell, with the MySQL I am using, the statement above would be equivalent to:

select * from pwdata where player = "some'string";

(using double quotes to wrap the selection string)

This syntax does seem to work with my MySQL installation, but I have only tested it with MySQL 4.0 and 4.1.

Roland Rosier
Back to top
View user's profile Send private message
dguntner



Joined: 31 Dec 2004
Posts: 116

PostPosted: Tue Mar 22, 2005 21:28    Post subject: Reply with quote

Therein lies the rub - not everyone is using MySQL at that current of a version.

Roland Rosier wrote:
As far as I can tell, with the MySQL I am using, the statement above would be equivalent to:

select * from pwdata where player = "some'string";

(using double quotes to wrap the selection string)


Now, you actually *used* the double quotes (changed aps_include's MySQL calls)? Or you found that changing SQL{Encode|Decode}SpecialCharacters() so that it uses '' instead of ~ for a single ' resulted in it treating the query as being the way you describe above?

I suspect they may have gone the ~ route because at the time it was written, MySQL wouldn't treat it that way (assuming that you didn't actually *use* double-quotes in your query).

Of course, making any such change now would be a real pain for anyone who has an established PW going. How do you make the mass change to your DB? Personally, I'd think it'd just be easier to put in a bit of code in the OnClientEnter script looking for a ~ in the character or player name, and booting the offending player. Smile

--Dave
Back to top
View user's profile Send private message
Roland Rosier



Joined: 06 Mar 2005
Posts: 5

PostPosted: Wed Mar 23, 2005 0:03    Post subject: Reply with quote

I didn't use double quotes in an NWNScript function, because they are used in NWNScript to delimit strings. I was typing example lines into a MySQL command line to test for equivalence; I was trying to see if you could effectively escape a single quote in a subsection of a string that is delimited by single quotes by prefixing it with another single quote.

And yes, for established PWs, such a change in the script would be a pain. However a one-time conversion could be achieved by putting the database through SQLDecodeSpecialChars; following that the SQLEncodeSpecialChars mapping would be 1:1.

I have seen players on other worlds like to use both ' and ~ in their names; hence I was wary of automatically booting names consisting of one or other of these characters. What would happen if their Player Name (not their Character Name) used a ~ (I don't know if it is allowed) but they might not wish to choose another Player Name?

Additionally, a configuration variable before the SQLEncodeSpecialChars and SQLDecodeSpecialChars could switch their behaviour to allow backwards compatibility ...

This was purely a suggestion. I am considering replacing SQLEncodeSpecialChars in my module and I wondered (a) if anyone else would like to do the same and (b) if there are any ramifications that I had not noticed.

It would also be of interest if other SQL implementations also permitted escaping a single quote in this fashion?

Roland Rosier
Back to top
View user's profile Send private message
Gryphyn



Joined: 20 Jan 2005
Posts: 431

PostPosted: Wed Nov 29, 2006 23:08    Post subject: Reply with quote

dguntner wrote:
I don't think MySQL would like two ' characters in a row, but I've never tested it. It uses a single quote to wrap around things that it's searching for. I'm not sure how it would take when given something like

select * from pwdata where player = 'some''string';

(that's two single quotes in the middle, not a double quote).

--Dave


That would be > some'thing < in the data.
The bit missing from the Enquote/Dequote is that it not just adding/removing a quote character from the beginning and end of a string. (a mouthful)
It's the double up of any quote character within that string.
the string > 'quoted'string' < requires > '''quoted''string''' < to get it into the database.
The tricky bit is when nasty people inject sql (now this shouldn't be an issue as you would be the nasty person - you're writting the code)
What this means is that they terminate the sql command early - and add their own sql in the rest of the string, typically this is done with > ;" < as the first few characters.
The same principle applies here when the data is returned to NWN, any > " < character may truncate the visible data.
> with"quote < returns as > with < -- I think NWNX handles this (it returns the #0 terminated string - less the #0)

The substitute technique used by NWNX has the advantage of maintaining the buffer (string) length, resizing a string is costly - replacing char(s) is not (by comparison) - but you really need three substitutions > ' <, > " < and > ; < characters should all be replaced with 'safe' alternatives.

NWNX uses an #0 terminated buffer - which inherently sets a length to the string [all-be-it flawed if you use color indicators in your strings]
At this point the enquote/dequote could take place (hidden from scripting)
The function being called should be smart enough to know how to handle special characters in code. Enquote before a write, dequote after a read.

More rumblings from the QA department
Cheers
Gryphyn
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Scripts and Modules All times are GMT + 2 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group