title: How to correctly create Web Service with WHERE clause preventing injection
body: <p>Not sure where to post this question, but I am concerned about the security of the answer so I targeted here.</p>
  
  <p>I have a web service running that reliably returns a dataset and allows me to provide a list now drop down list box of records returned. </p>
  
  <p>I would like to capture the users selection from the DDL and call another web service to return detailed information regarding the selection. Hence a WHERE stmt in the query of the web service. I am of course worried about SQL injection. But I would ike to at least get the SQL stmt working.</p>
  
  <p>The selection is a string field type, and looks like this,</p>
  
  <pre><code>&lt;WebMethod()&gt; Public Function getDBrecords(ByVal FileName As String) As DataSet
      Return GetDataSet("SELECT ID, ptMaster_ID, StrName, LngText, ShrtText,  Lcode, Name, FROM tblMstStrng WHERE FileName = """ &amp; FileName &amp; """; ")
  End Function
  </code></pre>
  
  <p>No luck, it crashes my app with a SOAP error. </p>
  
  <p>"Unable to automatically step into the server. Attaching to the server proces failed. A dbugger is already attached."</p>
  
  <p>And after I clear that Message box....</p>
  
  <pre><code>System.Web.Services.Protocols.SoapException was unhandled
    Actor=""
    Lang=""
    Message="System.Web.Services.Protocols.SoapException: Server was unable to process request. ---&gt; System.Data.SqlClient.SqlException: Invalid column name 'DD_EBSKW20_380_db.pts'.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)    at stringInfo.GetDataSet(String strSQL) in C:\Visual Studio 2008\WebSites\WebSite2\App_Code\stringInfo.vb:line 32    at stringInfo.getDBrecords(String dbName) in C:\Visual Studio 2008\WebSites\WebSite2\App_Code\stringInfo.vb:line 74    --- End of inner exception stack trace ---"
    Node=""
    Role=""
    Source="System.Web.Services"
    StackTrace:
         at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
         at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
         at MultiLangWeb.localhost.stringInfo.getDBrecords(String dbName) in C:\Visual Studio 2008\Projects\MultiLangWeb\MultiLangWeb\Web References\localhost\Reference.vb:line 118
         at MultiLangWeb.Main.Button6_Click(Object sender, EventArgs e) in C:\Visual Studio 2008\Projects\MultiLangWeb\MultiLangWeb\Main.Designer.vb:line 388
         at System.Windows.Forms.Control.OnClick(EventArgs e)
         at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
         at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)
         at System.Windows.Forms.Control.WndProc(Message&amp; m)
         at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
         at System.Windows.Forms.Button.WndProc(Message&amp; m)
         at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
         at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
         at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)
         at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
         at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
         at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
         at MultiLangWeb.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
         at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
         at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
         at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.ThreadHelper.ThreadStart()
    InnerException: 
  </code></pre>
  
  <p>Everything works for other web services if I remove the query with the WHERE clause.</p>