How to deal with UTF-16 strings and SQL

[Red Squirrel]

I am working on a game server application and converting it from a file based save to a SQL based save (the original coders should of done this from the start) now my issue is, the app supports UTF-16 chars. Now I have 65535 chars to check against or/and escape for SQL injections/errors. Is there a method in C# that will filter this for me? There are certain UTF-16 chars that cause errors such as a reversed version of ` and probably many others.

 

[MrChad]

Just construct your query using parameters.

http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

 

[KB]

If you use parameters you don't have to worry about the special characters that will cause errors.

 

[Red Squirrel]

I'll read up on those and see how it works, thanks.

 

[presidentender]

Originally posted by: RedSquirrel
I'll read up on those and see how it works, thanks.

Your life is about to get so much easier.

 

[Red Squirrel]

I've been reading up a bit on these, now I'm wondering, is there a way to get the final string result instead of submitting it to SQL right away?

In my program, I have a failsafe where if the SQL server is down, the query is stored in a file, then when the server comes back up, the file is flushed. So I need to store the queries in the file after they have been filtered out. In fact even if the SQL server is up the queries are actually in a string array as they are generated by another process then the thread picks them up and submits them to SQL. So the process that puts them in the array needs to do the injection tests and other escapes. Can this be done with parameters?


Basically this is what I need to be able to do:


Gather all data for the fields and filter the values of each field (some may be user inputted data) -> Create query -> Store query in a string -> send it to another process -> other process tries to submit to SQL, if server is down, stores it to file for later on.

 

[MrChad]

I would look at a Message Queue solution or simply serialize an object or a series of objects to disk containing my query parameters.

 

[Red Squirrel]

Wow this is really getting complicated. All this code just to submit a SQL query with quotes:


MySqlConnection connector = new MySqlConnection("SERVER=borg.loc;PORT=3306;PROTOCOL=tcp;DATABASE=aov_local;UID=root;PASSWORD=password;");
connector.Open();

MySqlCommand Command = connector.CreateCommand();

Command.CommandText = "UPDATE test set b=?b WHERE a=1;";

MySqlParameter oParam = new MySqlParameter();
oParam.ParameterName="?b";
//oParam.SqlDbType = MySqlDbType.NText;
oParam.Value = "this is a test with chars that would normally fail: ' \" `\n\n\n some text";
Command.Parameters.Add(oParam);


Console.WriteLine(Command.CommandText);


DataSet oDataSet = new DataSet();
MySqlDataAdapter oAdapter = new MySqlDataAdapter();
oAdapter.SelectCommand = Command;
oAdapter.Fill(oDataSet);

Console.WriteLine(Command.CommandText);




There's got to be an easier way. This will get even more complicated when I get into bigger queries. Isin't there just some kind of filter command?

Like I've been passing my user inputted data through this function:


public static string Sanitize(string instr)
{
string ret=instr;

ret=ret.Replace("\\","\\\\");
ret=ret.Replace("\"","\\\"");
ret=ret.Replace("`","\\`");
ret=ret.Replace("\'","\\'");

return ret;
}


Is there something built in that does a better job and also deals with UTF16?

Worse comes to worse, how do I manually escape each char? How do I loop through a C# string at the UTF16 char level? I'll write my own function once I discover all the UTF16 chars that need to be escaped. It will be long though... 65535 chars to go through.

 

[MrChad]

I don't see what's complicated. Create a command, create your parameter object(s), execute the command. It's far simpler and less error-prone than trying to sanitize your SQL strings manually. There's no need to sanitize your input using parameters.

 

[Red Squirrel]

Look how many lines that is for a single query. Can't there just be a filter function? Then I could easily go something like:


MySqlConnection connector = new MySqlConnection("SERVER=borg.loc;PORT=3306;PROTOCOL=tcp;DATABASE=aov_local;UID=root;PASSWORD=password;");
connector.Open();

MySqlCommand Command = connector.CreateCommand();

Command.CommandText = "UPDATE test set b=" +Filter("some string with bad data \" \' `")+ " WHERE a=1;";

connector.ExecuteReader(Command);


Much cleaner and easier to manage.

Either way I'll probably write a wrapper class anyway, but just hoping there's a simple filter function I can use that way I don't need to redesign my entire code to use my wrapper class. I'm currently just dealing with the strings directly and build the query as I go.

I also need to somehow get the final text result as my debug logs show all the SQL queries that are sent to the SQL server. This is why I want to deal with text and not .net specific stuff. I rather work at a more raw level.

 

[MrChad]

Well, not exactly.

You'll get better performance using parameters. Behind the scenes, ADO.NET can pre-compile the SQL statement "UPDATE test SET b = ? WHERE a = 1" and re-use it for future use. This is especially useful for queries that are repeated often. When you use string substitution, you lose any caching benefits that could occur. Using parameters is faster and safer than string substitution.

Parameterized queries are not a ".NET specific" thing, they exist in virtually every database library, whether it's .NET, Java, or any other modern programming language.

 

[Markbnj]

A filter function could remove illegal characters and check for invalid syntax, but it could not protect against SQL injection attacks. It doesn't know what your intent is. SELECT UserId, Password FROM User WHERE Login IS NOT NULL" looks the same to it as any other SQL syntax.

If the bulk of your query is the same on every execution, saving only that you substitute values, then parameterize it. If the queries contain arguments that are supplied by the user interactively, then definitely parameterize them. If they are different every time, as in an ad hoc query tool, then you have to take a different approach.

 

[GodlessAstronomer]

If you're working in .NET, use the framework! It's not a big scary evil thing to be avoided, the framework is reliable, fast and optimised by programmers who are much smarter than you or I. ADO.net is really very nice.

 

[Red Squirrel]

Originally posted by: Markbnj
A filter function could remove illegal characters and check for invalid syntax, but it could not protect against SQL injection attacks. It doesn't know what your intent is. SELECT UserId, Password FROM User WHERE Login IS NOT NULL" looks the same to it as any other SQL syntax.

If the bulk of your query is the same on every execution, saving only that you substitute values, then parameterize it. If the queries contain arguments that are supplied by the user interactively, then definitely parameterize them. If they are different every time, as in an ad hoc query tool, then you have to take a different approach.

I would just filter the data that actually goes in the field ex: UPDATE table set field='text';

I would just pass text through the filter. The reason I need to go this way is I need to have access to the final string. When using parameterized queries I don't have that access. I have various debugging systems that need to know the exact strings going in the DB which makes it tons easier to debug if something goes wrong, as this is in middle of development stage, so things will go wrong.

If there's some kind of way to get the final string, then maybe it will work. But really is it that hard to get a list of bad chars in UTF16 and how to properly escape them? That would be a hell of allot easier to do. I know there is a special quote that is oposite of ` but not sure of others.

 

[imported_Dhaval00]

I am still a bit hazy about your UTF16 requirement. Also, what do you mean by the "final string?" Is it the blurb that contains the entire command that was issued via ADO.NET? Maybe you can give us all an example about what you exactly need to achieve...

I can think of using RegExs, SMO, or LINQ, but I don't want to postulate anything until I understand what you need.

 

[Red Squirrel]

Basicaly this is how my app works:

It is a game server and every 5 seconds in the main thread all changed mobiles/items are put into a special queue, right now the SQL query is also generated at that point and each field is filtered through my basic filter (which is what I want to improve).

Then another thread goes through this list and submits the queries. If they fail due to loss of connectivity, they are put in a file (this is another reason I need the actual query text). This process also checks the file to see if it exists and if it is it ensures those queries get submitted to SQL before the ones in memory. The ones in memory just get appended at the end of the file until it "catches up". This is done for failover reasons, so that a down SQL server does not cause my app to stop functioning or the memory buffer to build up with too many queries.

Durring all of th ese processes the queries themselves are also written to debug logs depending on which debug options I enable.

The reason for UTF-16 is that the game works on that, otherwise I'd just go UTF8 as it's way easier to work with. C# strings seem to be UTF-16 anyway so either way I'm stuck with that. There are chinese people who play that game so it is kinda important to have support for it. Ex: Player names that have chinese characters in them, and what not.

Now my plan B is to just store my data as a stream of ints, where each int is a char value, but that would be unefficient.

 

[crackerjacks]

Use the parameters. It's not too much work and your DBA will thank you.

I'm also leary about having the app running while the database is down. If a "queued" transaction deletes a record and a later transaction tries to update that deleted transaction your data can be in a very bad place. But I'm sure you know your application better than I do so maybe this won't be an issue, just a thought.

 

[Markbnj]

Originally posted by: RedSquirrel

Originally posted by: Markbnj
A filter function could remove illegal characters and check for invalid syntax, but it could not protect against SQL injection attacks. It doesn't know what your intent is. SELECT UserId, Password FROM User WHERE Login IS NOT NULL" looks the same to it as any other SQL syntax.

If the bulk of your query is the same on every execution, saving only that you substitute values, then parameterize it. If the queries contain arguments that are supplied by the user interactively, then definitely parameterize them. If they are different every time, as in an ad hoc query tool, then you have to take a different approach.

I would just filter the data that actually goes in the field ex: UPDATE table set field='text';

I would just pass text through the filter. The reason I need to go this way is I need to have access to the final string. When using parameterized queries I don't have that access. I have various debugging systems that need to know the exact strings going in the DB which makes it tons easier to debug if something goes wrong, as this is in middle of development stage, so things will go wrong.

If there's some kind of way to get the final string, then maybe it will work. But really is it that hard to get a list of bad chars in UTF16 and how to properly escape them? That would be a hell of allot easier to do. I know there is a special quote that is oposite of ` but not sure of others.

You can capture the exact command text in other ways (i.e. SQL Trace/Profiler) so if this is just about debugging I wouldn't let that drive your design.

 

[Red Squirrel]

Originally posted by: crackerjacks
Use the parameters. It's not too much work and your DBA will thank you.

I'm also leary about having the app running while the database is down. If a "queued" transaction deletes a record and a later transaction tries to update that deleted transaction your data can be in a very bad place. But I'm sure you know your application better than I do so maybe this won't be an issue, just a thought.

Yeah that's been considered. I just have to make sure the queries go in proper order. An issue that could arrise though is if a serial number (the unique key) is reused. I need to look more into that.

The text queries are a requirement for this to work though. Later on this app may run with a seperate SQL server so I don't want an outage of that server to cause an outage to the game server. Eventually I may even setup a fail over system if the game server itself goes down.

 

[MrChad]

Originally posted by: RedSquirrel

Originally posted by: crackerjacks
Use the parameters. It's not too much work and your DBA will thank you.

I'm also leary about having the app running while the database is down. If a "queued" transaction deletes a record and a later transaction tries to update that deleted transaction your data can be in a very bad place. But I'm sure you know your application better than I do so maybe this won't be an issue, just a thought.

Yeah that's been considered. I just have to make sure the queries go in proper order. An issue that could arrise though is if a serial number (the unique key) is reused. I need to look more into that.

The text queries are a requirement for this to work though. Later on this app may run with a seperate SQL server so I don't want an outage of that server to cause an outage to the game server. Eventually I may even setup a fail over system if the game server itself goes down.

Doesn't your application need to read data from the database server? You can queue up INSERTS and UPDATES, but you're SOL for SELECTS.

In general, a database-driven app is hosed if the database goes offline. As crackerjacks said, if you try to engineer around that, you are likely setting yourself up for unexpected behavior and unwieldy code. If uptime is that critical, you're better off setting up a DB cluster and failover system.

 

[imported_Dhaval00]

As others have said, use parameters to avoid injection attacks; especially so, since this is a game server. In case of a server failure, you can create a method that loops over the SqlCommand's parameter collection and builds a "command string" by replacing the parameter name (within the CommandText) with the value of the parameter.

You might have to work this out via trial and error. I think the logic above may actually open you to injection attacks (someone please verify).

Beyond that, you can use RegExs on the actual values to include only the UTF16 characters. However, I am still not sure how this affects you, since .NET strings are UTF16 natively.

 

[Red Squirrel]

Originally posted by: MrChad

Originally posted by: RedSquirrel

Originally posted by: crackerjacks
Use the parameters. It's not too much work and your DBA will thank you.

I'm also leary about having the app running while the database is down. If a "queued" transaction deletes a record and a later transaction tries to update that deleted transaction your data can be in a very bad place. But I'm sure you know your application better than I do so maybe this won't be an issue, just a thought.

Yeah that's been considered. I just have to make sure the queries go in proper order. An issue that could arrise though is if a serial number (the unique key) is reused. I need to look more into that.

The text queries are a requirement for this to work though. Later on this app may run with a seperate SQL server so I don't want an outage of that server to cause an outage to the game server. Eventually I may even setup a fail over system if the game server itself goes down.

Doesn't your application need to read data from the database server? You can queue up INSERTS and UPDATES, but you're SOL for SELECTS.

In general, a database-driven app is hosed if the database goes offline. As crackerjacks said, if you try to engineer around that, you are likely setting yourself up for unexpected behavior and unwieldy code. If uptime is that critical, you're better off setting up a DB cluster and failover system.

Actually the SQL is only used to save data as the game progresses (in case it crashes or goes down, it has a very recent save point) the SELECTs only happen at load time and all the data stays in memory. This model is not the best as .net is memory limitations but that's how this app is designed.

My idea should work as long as I can make sure to filter out ALL bad chars. I have 65535 of them to go through, so I'm hoping there's a built in function somewhere.

 

[clamum]

Ehhhh, just not a great idea to not use parameterized queries unless you know 110% you don't have to worry about bad strings. Even then, I wouldn't feel sorry for a database being fuct over by not using them. There's a reason why they are part of the .NET framework (I agree with GodlessAstronomer).

Perhaps look at .NET 3.0/3.5 and LINQ? Makes writing queries fucking stupidly easy.