|
NEVER use string concatenation to build SQL queries. ADO.NET supports parameterized queries and you should use them. What you're doing allows for easy SQL injection attacks.
Search previous comments for more reasons in the billions of times I've had to say this. Your code - and similar code you may write - is extremely insecure and does not follow good coding practices. Mike's reply right below yours mentions as much, too.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
It's best to use parameterised queries, for example:
SqlCeCommand cmd =
new SqlCeCommand(
"SELECT Priority, Problem, EmailClient " +
"FROM JobDescription " +
"WHERE UserEngineerId = ? AND StartDate >= ? " +
"ORDER BY Priority" );
SqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.Int );
param1.Value = userEngineerID;
cmd.Parameters.Add( param1 );
SqlCeParameter param2 =
new SqlCeParameter( "@date", SqlDbType.DateTime );
param2.Value = DateTime.Today;
cmd.Parameters.Add( param2 );
cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter( cmd );The two parameters substitute for the ? in the query string, in the order they're added to the Parameters collection.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
That is impossible it does still not work, now I wrote it in that way, again without the date property:
dataSet = new DataSet("Jobs");
SlCeCommand cmd =
new SqlCeCommand(
"SELECT Priority, Problem, EmailClient " +
"FROM JobDescription " +
"WHERE UserEngineerId = ? " +
"ORDER BY Priority" );
string userEngineerId = "hattl70";
SqlCeParameter param1 =
new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar );
param1.Value = userEngineerId;
cmd.Parameters.Add( param1 );
cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter( cmd );
daptJobs.Fill(dataSet,"Jobs");
DataTable dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;
The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar.
Does anybody have anymore ideas what went wrong here?
best regards
patrick
|
|
|
|
|
pat270881 wrote:
The SqlCeException occured again.
Does anybody have anymore ideas what went wrong here?
What are the details of the exception? There will be a message at least, some details about the state of the database and so on. These really help to track down problems.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
Okay the error notification looks as follows:
Unexpected Error in
MobileHelpDesk.exe
SqlCeException
SqlCeCommand::InitializeCommand+0xf9
SqlCeCommand::ExecuteCommand+0xa
SqlCeCommand::ExecuteReaderInternal+0x30
SqlCeCommand::ExecuteReader+0x13
SqlCeCommand::System.Data.IDbCommand.ExecuteReader+0x7
DbDataAdapter::Fill+0x37
DbDataAdapter::Fill+0x85
DbDataAdapter::Fill+0x11
Are anybody able to see with the help of this error what went wrong??
best regards
patrick
|
|
|
|
|
SQL Server supports named paramters. Use ? for OleDbCommand.
Your expression should look like this:
SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE UserEngineerId = @UserEngineerId
ORDER BY Priority"); When you specify a variable-width field type like SqlDbType.NVarChar, you should also use the overloaded SqlCeParameter constructor (or SqlCeCommand.Parameters.Add method) to specify the number of characters:
SqlCeParameter id = cmd.Parameters.Add("@UserEngineerId", SqlDbType.NVarChar, 40);
id.Value = "hattl70";Read the documentation for either SqlCeCommand.Parameters or SqlCommand.Parameters for more information and examples of how to use parameterized queries with SQL Server (CE).
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
Mr. Stewart i already tried the version with parameter, but the same error occurs again which i mentined in a previous posting. I wrote the code in that way: (UserEngineerId is EngineerUserId).
SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE EngineerUserId = @EngineerUserId
ORDER BY Priority");
SqlCeParameter id = cmd.Parameters.Add("@EngineerUserId", SqlDbType.NVarChar, 50);
id.Value = "hattl70";
dataSet = new DataSet("Jobs");
cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter(cmd);
daptJobs.Fill(dataSet,"Jobs");
dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;
The problem ist that i could no other error then the Where statement because if i don't use this statement the code works without problems.
I do not understand this phenomen...
regards
patrick
|
|
|
|
|
Whether your statement works with or without parameters doesn't matter: NEVER, EVER use concatenated SQL expressions unless you like have credit card numbers stolen, employee hours changed, wages and salaries emailed throughout the company, and entire tables and even databases dropped. One guy who posted shortly after you makes it even easier for crackers since he's running the commands as the 'sa' account (which is even worse since he doesn't have a password for 'sa'). Always use the least amount of privileges required to run a particular command, even with SQL Server CE.
What exception is occuring and on what line? Does the user you're running under have permissions to the database objects required? Are you sure that EngineerUserId is a string (in most database designs anything with "id" is typically an integer or GUID). Are you sure that it's 50 characters wide and that it's an NVarChar (and not a VarChar, "N" means Unicode or "nationalized").
There could also be a problem since your DataSet and DataTable share the same name. If you don't care about the DataSet name, then don't name it (just use default instantiation, i.e. new DataSet()).
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
The following error occurs:
Error #1 of 1
Error Code: -2147217900
Message: There was an error parsing the query. [Token line number, Token line offset,, Token in error,,]
Minor Err.: 25501
Source: Microsoft SQL Server 2000 Windows CE Edition
Numeric Parameter: 3
Numeric Parameter: 49
Error Parameter: @EngineerUserId
As is said it must lie with this Where Statement. Do you have further ideas? - I alread checked your remarks from the previous posting
regards
patrick
|
|
|
|
|
All the syntax looks right. The next thing I would check is to make absolutely sure you spelled all your field and table names correctly in your SELECT statement. Don't think you did, know you did. Go back to the SQL Enterprise Table Designer and look and compare.
The first rule of troubleshooting - don't think, know...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I know that i spelled all my fields right. I also use Replication, can this causes the error??? But i do not use Identity property in the context of my Replication, the JobId is a uniqueidentifier.
But I do simply not understand why the replication should cause this problem, because when I quote directly a value in the Where Statement - for example the value hattl70, then no error occured...
regards
patrick
|
|
|
|
|
Replication being turned on will not generate this kind of error for you. What do you mean by "when I quote directly"?
There has got to be something wrong with the parameter @EngieerUserId. You defined your parameter object to be NVARCHAR(50). Are you absolutely positive that this is how it is defined in the table structure? I'd be willing to bet it isn't...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Really, that specification of that field is NVarChar(50) - I can send you the tables, if you want.
With direct mean, if I write it in that way
SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE EngineerUserId = 'hattl70'
ORDER BY Priority");
cmd.Connection = new SqlCeConnection( strConn );
dataSet = new DataSet();
daptJobs = new SqlCeDataAdapter(cmd);
daptJobs.Fill(dataSet,"Jobs");
dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;
MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs");
When i quote a certain value of the field, for example hattl70 than the corresponding rows are display without an error.
Can you explain that yourselves?? Believe me I tried so much, nearly the whole day.
regards
patrick
|
|
|
|
|
Turns out SQL Server CE doesn't support named parameters. I found that out by reading it quick, something the original poster obvious hasn't done.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
Damn.  Neither did I. Who would have thought! I can see Jet not supporting them, but SQL CE?
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
That's fine - it's not our problem. We're just trying to help, but trying to help one's self should always be the first step. 
SQL CE has to pack a lot of functionality into a little space. Just look at .NET CF. I didn't realize it didn't use named parameters either since I've never really used it much (evaluated it once for a project that I never got around to), but it certainly doesn't surprise me.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
Upon reading the documentation for the SqlCeParameter and SqlCeCommand in the .NET Framework SDK - which is your job and you should've done - SQL Server CE doesn't use named parameters like SQL Server. You instead use ? and add your parameters in order (still with names) of the ? (question marks). Read the documentation for the SqlCeCommand.Parameters property in the .NET Framework SDK and you'll find a good example.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
The first time I wanted it to do it that way, but one has quoted me that I can do it with this parameter. Now I implemented it like in the example:
string EngineerUserId = "hattl70";
SqlCeConnection conn = new SqlCeConnection(strConn);
conn.Open();
SqlCeCommand command = conn.CreateCommand();
command.CommandText = "SELECT JobId, Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = ?";
command.Parameters.Add( "@EngineerUserId", EngineerUserId);
command.Prepare();
command.ExecuteNonQuery();
dataSet = new DataSet();
daptJobs = new SqlCeDataAdapter(selectCommand, strConn);
daptJobs.Fill(dataSet,"Jobs");
dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;
MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs");
But it would have wondered me very much if it would work. ( No the following error occurs:
There is a file sharing violation. A different process might be using the file.
Minor Err.: 25035
Is it really possible that anything else went wrong?
patrick
|
|
|
|
|
See my comment below about the file being in use.
The problem is not related to using or not using parameters. ALWAYS use parameters when possible (for field values and conditionals in WHERE clauses). Except for the possibility of SQL injection attachs when using string concatenation and not properly encoding and checking input (like with PHP or back in the glory days of the original ASP) the resultant SQL expression would be the same. Parameters mitigate almost all - if not all - SQL injection attacks.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
And i have no more connection open, only this one. (Because the error indicates that there is another process which has access to the file)=
|
|
|
|
|
You do realize that when you close a PocketPC app by default the window is only closed, right? The application is not terminated (unless you explicitly exit the process). This could explain why the file is in use. Other processes may be using the file, too. If you have added a connection between this file and SQL Server Enterprise Manager, for example, it may have a lock on the file (or your application is requesting an exclusive lock on the file).
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
No, I have checked all these things. There exists only one SQL connection. And where and for which purpose should exist a connection between the SQL Server and the SQL Server Ce. I have a connection between the two only when I press the synchronize button.
when I do the same with this code:
string EngineerUserId = "hattl70";
SqlCeCommand cmndDB = new SqlCeCommand();
cmndDB.Connection = connection;
cmndDB.CommandText =
"SELECT JobId, Priority, Problem, EmailClient" +
"FROM JobDescription" +
"WHERE EngineerUserId=" + EngineerUserId;
cmndDB.ExecuteNonQuery();
dataSet = new DataSet();
daptJobs = new SqlCeDataAdapter(selectCommand, strConn);
daptJobs.Fill(dataSet,"Jobs");
dtabJobs = dataSet.Tables["Jobs"];
dgridDisplay.DataSource = dtabJobs;
MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs");
this.connection.Close();
I got not the file violation, but the error "There Was an error parsing the query" Minor Err:: 25501.
I do simply what know what else I should try...? (
|
|
|
|
|
You don't get the file sharing violation because your query couldn't be parsed, which happens before it can execute, which is when it accesses the SQL CE database. If you insist on opening your program and the database up for SQL injection attacks, then remember that EngineerUserId is a string - not an integer - so you need to quote it:
WHERE ENgineerUserId = '" + EngineerUserId "'"; Now all someone would have to do is set EngineerUserId to the following and you or your company is in trouble:
asdf' AND 1=1; DROP TABLE JobDescription; -- Assuming EngineerUserId comes from a TextBox, it's no problem to set the text to the string about. Now, no more "JobDescription" table. If they want - and most RDBMS's support schema information - they could get a catalog of all the tables and their columns and, if available, start querying and emailing salaries, changing salaries, or making purchases on company goods, etc.
Use parameterized queries - seriously - and make sure all other applications are shutdown. If there is a file sharing violation then another process or thread is most definitely using the SQL CE database file. Make sure you read about the connection string properties and be sure you pass any file sharing property values necessary so that you're not trying to obtain an exclusive lock on the file.
You should start by reading the documentation for the SqlCeConnection class in the .NET Framework SDK.
This posting is provided "AS IS" with no warranties, and confers no rights.
Software Design Engineer
Developer Division Sustained Engineering
Microsoft
[My Articles] [My Blog]
|
|
|
|
|
Mr. Stewart I finally fixed the problem! The problem was in the SQL Query, very trivial.
Thank you very much for your support!!
regards
patrick
|
|
|
|
|
You need to also supply the size of an NVarChar field. I assumed Int because you didn't say. If the field is declared as nvarchar(20), use
SqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.NVarChar, 20 ); SQL Server CE 2.0 doesn't care what you set the name of the SqlCeParameter to, and accepts only a ? character as a parameter marker (as you found out). This is different from the desktop SQL Server, which uses named parameters with a leading @ symbol.
You may also need to call cmd.Connection.Open() before calling Fill. If an exception is thrown, check the SqlCeException object's Errors collection for more information.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
