{"id":1241,"date":"2014-05-06T16:02:08","date_gmt":"2014-05-06T16:02:08","guid":{"rendered":"https:\/\/ibex.tech\/cloud\/?p=1241"},"modified":"2022-02-17T07:14:03","modified_gmt":"2022-02-17T07:14:03","slug":"sanitizing-queries","status":"publish","type":"post","link":"https:\/\/ibex.tech\/cloud\/sql-server\/queries-microsoft-sql-server\/select-statements\/sanitizing-queries","title":{"rendered":"Sanitizing Queries"},"content":{"rendered":"<p>\nUse parameter based queries instead of trying to sanitize strings &#8211; its much safer and easier.\n<\/p>\n<p>\nGood resources:\n<\/p>\n<p>\n<a href=\"http:\/\/stackoverflow.com\/questions\/249567\/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-sharp-code\">http:\/\/stackoverflow.com\/questions\/249567\/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-sharp-code<\/a>\n<\/p>\n<h4>\nC++ .Net example of a parameter based query<br \/>\n<\/h4>\n<pre>\r\n<code>\r\n\tSqlConnection1-&gt;Open();\r\n\tSqlClient::SqlDataAdapter ^SqlDataAdaptor1 = gcnew SqlClient::SqlDataAdapter();\r\n\tSqlDataAdaptor1-&gt;MissingSchemaAction = MissingSchemaAction::AddWithKey;\r\n\tDataSet ^DataSet1 = gcnew DataSet();\r\n\tSqlClient::SqlCommand ^SqlCommand1 = gcnew SqlClient::SqlCommand();\r\n\tSqlCommand1-&gt;Connection = SqlConnection1;\r\n\tSqlClient::SqlCommandBuilder ^CmdBuilder1 = gcnew SqlClient::SqlCommandBuilder();\r\n\tCmdBuilder1-&gt;DataAdapter = SqlDataAdaptor1;\r\n\r\n\t\/\/----- GET TABLE -----\r\n\tSqlCommand1-&gt;CommandText = &quot;SELECT * FROM MyTable WHERE MyColumnName = @Param1&quot;;\r\n\tSqlCommand1-&gt;Parameters-&gt;AddWithValue(&quot;@Param1&quot;, SomeVariableValue);\r\n\r\n\tSqlDataAdaptor1-&gt;SelectCommand = SqlCommand1;\r\n\tSqlDataAdaptor1-&gt;Fill(DataSet1, &quot;MyTable&quot;);\r\n\tDataTable ^Table1 = DataSet1-&gt;Tables[\"MyTable\"];\r\n\r\n\t\/\/----- READ ROWS -----\r\n\tif (Table1-&gt;Rows-&gt;Count)\r\n<\/code><\/pre>\n<h5>\nThese are the important lines:<br \/>\n<\/h5>\n<pre>\r\n<code>\r\n\tSqlCommand1-&gt;CommandText = &quot;SELECT * FROM MyTable WHERE MyColumnName = @Param1&quot;;\r\n\tSqlCommand1-&gt;Parameters-&gt;AddWithValue(&quot;@Param1&quot;, SomeVariableValue);\r\n<\/code><\/pre>\n<p>\nThere is no need to add single quotes around string etc parameters in the Command Text, this is fine:\n<\/p>\n<pre>\r\n<code>\r\n\tSqlCommand1-&gt;CommandText = &quot;SELECT * FROM MyTable WHERE MyColumnName = @Param1&quot;;\r\n\tSqlCommand1-&gt;Parameters-&gt;AddWithValue(&quot;@Param1&quot;, SomeString);\r\n<\/code><\/pre>\n<p>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use parameter based queries instead of trying to sanitize strings &#8211; its much safer and easier. Good resources: http:\/\/stackoverflow.com\/questions\/249567\/algorithm-to-avoid-sql-injection-on-mssql-server-from-c-sharp-code C++ .Net example of a parameter based query SqlConnection1-&gt;Open(); SqlClient::SqlDataAdapter ^SqlDataAdaptor1 = gcnew SqlClient::SqlDataAdapter(); SqlDataAdaptor1-&gt;MissingSchemaAction = MissingSchemaAction::AddWithKey; DataSet ^DataSet1 = gcnew DataSet(); SqlClient::SqlCommand ^SqlCommand1 = gcnew SqlClient::SqlCommand(); SqlCommand1-&gt;Connection = SqlConnection1; SqlClient::SqlCommandBuilder ^CmdBuilder1 = gcnew SqlClient::SqlCommandBuilder(); CmdBuilder1-&gt;DataAdapter [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[124,6],"tags":[],"class_list":["post-1241","post","type-post","status-publish","format-standard","hentry","category-queries-microsoft-sql-server","category-select-statements"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1241","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/comments?post=1241"}],"version-history":[{"count":3,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1241\/revisions"}],"predecessor-version":[{"id":1260,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/1241\/revisions\/1260"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/media?parent=1241"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/categories?post=1241"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/tags?post=1241"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}