NOTE- The OutPut Type, SqlParameter Array is designed for applications that use SQL Helper Classes. It's assumed that the Helper Class will enumerate the array and add the parameters to the query.
SqlParameter Array Output Type is also usefull when you're not working with ADO.net or perhaps not even coding in .net. The actual SQL in any platform is not that variable so by using this Output Type you have your query parameterised and now you will simply edit the query to suit the development platform youre using. Even if you're using JDBC, the result delivered from fix-SQL will save a lot of time. We will integrate with ORACLE, ODBC, JDBC (named parameters) and others in the near future.
This video will show you the simple process for converting an unparameterised SQL Query into a Type Safe, parameterised Query
NOTE- When SqlParameter Arrays are used to integrate with Sql Helper Classes it's important to take a good look at the method signatures used in the Helper Class. The signature is unpredictable by fix-SQL so you need to tag any variables that are passed in which are positioned in the SQL text. For example, a method which looks like: RunSqlNonQuery(cmdText,sqlPars,dbconnection) when it's called it could look like RunSqlNonQuery("SELECT * from MyTable where CustName='mike' and CompanyName='fix-SQL',dbconnection)") The array would be optional so now dbconnection follows a comma and can be assumed incorrectly, to form part of the SQL text. This will fail fix-SQL processing and the fix is very simple. In the setup wizard add dbconnection to the Excluded List.
A video showing the Manual Processing Option for Query Conversion
fixSQL can revert a parameterised ado.net SQL query back to it's unparameterised state. You would do this to run the query in SSMS for testing or simply to better understand the query before editing. NOTE- fix-SQL correctly handles User Defined Types when they are registered in the database. Also, all schema information is loaded on launch. This means that using Schema names in queries is very simple and best practice. When you precede a table name with it's schema name you save a reasonably costly transaction on Sql Server and this helps performance. Autocomplete operates on Schema name when its used and when you place a period it will show all tables belonging to that Schema. These days databases often use multiple Schema's.'
How to Revert a Query back to it's Unparameterised format.
SQL Injection NOTES
Sql Injection is a very serious topic and what is detailed here only relates to the use of parameterisation and Type Safe strategies to help avoid Sql Injection attacks.
Here are two steps you can take to significantly harden data against Sql Injection attacks.
ONE
Make sure that every SQL query in client or web applications are parameterised. Parameters are non executable code so inserting ;DROP Customers willl have no effect unless it is later concatenated into a query text. Parameters also have the benefit of lifting performance. You should make sure that any text which is a user input is verified. Commonly this is done through a verification class that checks the text for sql commands. In the Options page of the fix-SQL project wizard there is a flag which will force fix-SQL to parameterise variables and literals instead of just variables. Many would argue that there is no need to parameterise literals however, there is a performance lift when you do
TWO
Type Safe Parameters
When you're parameters include datatype and length attributes your data is safer. Several Sql Injection attacks only work when parameters aren't Type Safe. Type Safe parameters make code easier to read however they also make schema edits a protential problem because they will need to be edited. A flag in Options will add a comment to all queries which is a searchable string so you can easily find queries which need editing to reflect database schema changes. You could also simply search code for type safe descriptor. There is an option in the Project Wizard Options to set date parameters to AddWithValue the reason for this is commonly in legacy applications you will find Date values used as a string and or as a date type. This is really annoying so the AddWithValue is a great way to avoid this even though it's not ideal. fix-SQL can avoid rewrites of legacy applications which need to be secured. The processs is massively faster and simpler with fix-SQL however this can still require a lot of time. If you keep getting errors in testing around date objects I would enable this flag.
Important to mention that a production database should have a well designed user security. If a Sql Injection inserts ;DROP TABLE Customers and the user doesn't have permissions to drop a table then the attack fails.
If any effort has been invested in a legacy app to parametrise SQL Queries you will often find that the parameters have been used as an AddWithValue style (in .net) or not Type Safe format. This video will show you how you can convert the parameters for a query with out having to change the entire SQL Query.
A video demonstrating how to edit SQL in fixSQL. Just paste the query into the editor all tables, views stored procedures and aliases will be added to the Tables and Aliases Treeview. All meta data has been loaded into Auto Complete menus. If you right click an entry you can select to highlight all instances of the selected item.
This video will run for 4 minutes
fixSQL will save you a lot of time when you're working with Stored Procedures and importantly ensures that code remains compliant.
This video will show you the simple processing for Stored Procedures. fixSQL will automatically write a .net usage of the procedure along with correctly formatted type Safe named parameters. fixSQL can also display the Stored Procedure definition code. The result is an ado.net process which will run however it is really intended to provide the elements for you to write your Stored Procedure.