-1

I'm a little in need of your help

In my web application I have this Select statement, but once I run it, it retrieves 0 data but when I try my Select statement in the database it has data in it, and my Select statement is correct, by the way my application is already published in the server.

Here's my code

string SelectStatement = "SELECT DATEDIFF(day, kg1653, GETDATE()) datenum, kg1635, (CASE WHEN kg1637 is null THEN 0 END) eis ";
string FromStatement = "FROM hsi.keygroupdata503 ";
string WhereStatement = "WHERE kg1235='" + _securityCode + "' and kg1241 is null";
_sqlDT = ConnectToDatabase(SelectStatement + FromStatement +  WhereStatement);

and here's my connection string

System.Data.Odbc.OdbcConnection _odbcConn = new System.Data.Odbc.OdbcConnection();
_odbcConn.ConnectionString = "MY DATABASE CONNECTION STRING";

System.Data.Odbc.OdbcDataAdapter _odbcA = new System.Data.Odbc.OdbcDataAdapter(sqlQuery1, _odbcConn);
DataTable _odbcDt = new DataTable();
_odbcA.Fill(_odbcDt);

return _odbcDt;

Can somebody please help me with this?

Thank you so much!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JanMichael
  • 31
  • 1
  • 12
  • 3
    Your query is wide open to a SQL injection attack. Don't concatenate strings like this when writing queries! – alroc Sep 10 '13 at 15:29
  • 2
    With C# 4.0 and SQL Server - ***why on earth*** are you using the legacy ODBC stuff?? – marc_s Sep 10 '13 at 15:30
  • @alroc Hi! Thank you for answering then what should i do? Is that causing my problem? – JanMichael Sep 10 '13 at 15:31
  • @marc_s because that would be the only way to connect to my database, from Web Server to AppServer. – JanMichael Sep 10 '13 at 15:32
  • I take it you have tried removing `WhereStatement` to see if anything is returned. If you are getting results from running the query directly then it suggests there is a difference... – Squirrel5853 Sep 10 '13 at 15:36
  • Use the debugger and extract the actual value of `sqlQuery1` and post that here. Otherwise, we're just guessing... – RBarryYoung Sep 10 '13 at 15:36
  • Hi I used htmlEncode for my textbox for SecurityCode because i have this kind of format in security code "&IyexP4y:&5Zuwe5FXf3" does it affect my code? – JanMichael Sep 10 '13 at 15:51

1 Answers1

0

When does sqlQuery1 get set to _sqlDT ... your best bet is to debug and see what the query is right on the line of it being called and copy it to run on the SQL server in case something else is updating it or _scurityCode is empty. Also if you have a test environment with similar table names, make sure you are connecting to the same live instance.

Side note, not foolproof but make sure _securityCode has a replace statement and change all single quotes to double quotes to work against SQL injection as the commentor above said.

sasonic
  • 764
  • 6
  • 13
  • Hi I used htmlEncode for my textbox for SecurityCode because i have this kind of format in security code "&IyexP4y:&5Zuwe5FXf3" does it affect my code? – JanMichael Sep 10 '13 at 15:52
  • Yes, SQL needs it in the same raw format, the ODBC driver will not translate the syntax. Make sure its correct in the calling service within the string before sending to the sql box. Just throw a breakpoint there and take a look at what SQL is getting. If you are trouble with decoding, see [link](http://stackoverflow.com/questions/122641/how-can-i-decode-html-characters-in-c) – sasonic Sep 10 '13 at 16:35
  • HTMLEncode & replacing characters isn't enough protection from SQL injection. Use Prepared Statements! – alroc Sep 10 '13 at 16:36