The OutPut Type, SqlParameter Array, is designed for applications that use SQL Helper Classes, data layers and applications that aren't using .net data objects. It's assumed that the Helper Class will enumerate the array and add the parameters to the appropriate command object.
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 quite consistent 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 Editor will save a lot of time (particularly when you're using named parameters). We will integrate with ORACLE, ODBC, JDBC and others in the near future.
When SqlParameter Arrays are used to integrate with Sql Helper Classes (or data layers) it's important to take a good look at the method signatures used in the Helper Class. The signature is unpredictable 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 run it could look like RunSqlNonQuery("SELECT * from MyTable where CustName='mike' and CompanyName=' fix-SQL Editor',dbconnection)"). When this is tokenised dbconection follows a comma and can be assumed incorrectly, to form part of the SQL text. This will fail fix-SQL Editor processing and the fix is very simple. In the setup wizard add dbconnection to the Excluded List. It will be ignored in all processing but will still appear in the resulting parameterized query when it's used or in a call signature.
fix-SQL Editor 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 step in the transaction on Sql Server and this helps performance. In fix-SQL Editor you can ignore preceding tables with the table schema becuase this is done for you. Importantly, this means you can autocomplete from the table name ignoring the schema name and it will be added on selection.
Sql Injection is a very serious topic and what is detailed here only relates to the use of parameterization and Type Safe strategies to help avoid Sql Injection attacks. Of all the Sql Injection defensive strategies, parameterization is the most tedious, error prone, labour intensive and effective when applied to SQL code that is not parameterized. It takes a toll on developers physically , mentally and emotionally and costs companies significant expenditure. Parameterizing non compliant SQL in apps is undoubtedly the worse job you will ever undertake in your career. I can't give you an exact number representing how much faster fix-SQL Editor is but from experience a large system can be completed in a month instead of a year. There is no system faster than fix-SQL Editor, just copy, paste, click the Run button, click the copy button and paste back to your application. By the way, strongly suggest you don't drag the mouse to select text from the source code. Far better to just click the start hold shift and click the end of the selection. Dragging the mouse over and over will take a toll on your hands.
Here are two steps you can take to significantly harden data against Sql Injection attacks.
ONE
Make sure that every SQL query in a client or web application (critically important on the web) is parameterized. Parameters are non executable code so appending ;DROP Customers to a SQL command will have no effect unless it is later selected in a query and then concatenated into a query text (very rare but possible). 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 such as Alter, Drop and of course escape characters ; in the input data. In the Options page of the fix-SQL Editor project wizard there is a flag which will force fix-SQL Editor to parameterise variables and literals instead of just variables. Many would argue that there is no need to parameterize literals however, there is a performance lift when you do this.
TWO
Type Safe Parameters
When you're parameters include datatype and length attributes the 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 meta data edits a potential problem because changes in data type etc need to be edited in the type safe parameters. A flag in fix-SQL Editor Options will add a comment to all queries which is a searchable string formatted as tablename.columnnName, you can easily find queries which need editing to reflect database schema changes. You could also simply search code for the type safe descriptor. A quick fix for queries that contain out of date datatype infor is to use the Revert Option to remove parameters from the query and then re run the Convert Process so you end up with a correct Type Safe info. When you are also looking at edits on the source code during the process fix-SQL Editor can help out. When you paste a query into the editor for editing all of the tables, columns, meta data and is loaded into the Tables and Aliases treeview ready for use. All this information is loaded into auto complete. At asny time during work on code you can just click the Load button at the bottom of the Tables and Views treeview and this will identify all tables and aliases in the query and load autocomplete menu's that you can use during edits.
There is an option in the Project Wizard Options to set date parameters to AddWithValue the reason for this is commonly in older applications you will find Date values used as a string and or as a date type. This is really annoying so using AddWithValue is a great way to avoid this even though it's not ideal. fix-SQL Editor can avoid rewrites of legacy applications which need to be secured. The processs is massively faster and simpler with fix-SQL Editor 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 well designed user security. If a Sql Injection attack appends ;DROP TABLE Customers and the user doesn't have permissions to drop a table then the attack fails.
When you're writing query text in fix-SQL Editor anytime you autocomplete 'JOIN' (of course you must have added a table with FROM first in the text) fix-SQL Editor will present a list of all the keys associated with the table as correctly formatted joins. When you select a join the related table will become active in Autocomplete immediately. You can even add an aliase in the FROM statement by just typing it into the query and all references to that table in the join will use the aliase. You can add an aliase for the table you want to join to by adding the table and aliase into the Tables and Aliases treeview. All the possible joins which have keys are displayed and aliases are used when they appear in the code or are listed in the Tables and Views treeview. Important to note that fix-SQL Editor will not display joins which destroy the integrity of the query. Each time you add a join you will see the system has taken account of that join in maintaining query integrity and included all new keys made possible by the previous join.
These output options are based on ADO.net Data Objects plus a Sql Array output Option. What you select here will dictate the style of the resulting parameters for the query. The SqlDataAdapter and SqlCommand options create parameters formatted for use in .Net code. The Sql Array option is designed to be more flexible. This option will create a parameter array which is used to connect Sql Helper classes and utilities and also for use in code that isn't Ado.net or even .Net. For example JDBC doesn't always use named parameters however the Sql Query will be very similiar if not the same. The parameter array created during the conversion will still be usefull although you will need to edit this. You will still save a lot of time. In .Net using helper methods the parameter array is added to a Command object normally through an array enumeration. This means there is a great deal of flexibility in what you can do with this code in JDBC and even easier, ODBC.
The Reset button will prepare fix-SQL Editor for a new conversion. It will reset a lot of memory stored objects and it's the best way to make sure everything is ready for a new transaction. The Reset ALL button does the same thing however it will also remove all Tables from the Tables and Aliases treeview on the right of the Form. The reason there are 2 options here is you may wish to keep a table\aliase entry in place because your'e using it again in the next transaction. Tables and Aliases entries will fill automatically when you paste a query into the editor. This means that aliases and tables are already loaded into autocomplete menu's. When you use an aliase followed by a period you see all columns for the table the alias represents. immediately. An alias in the query will be loaded and autocomplete without any additional action by the user.
Undo Last Action will undo the last entry into the editor. Undo Last Parameter is enabled when you are using Manual Conversion. This menu item will remove the last parameter pair you entered into the editor from memory and the editor. Recover Original Query will replace the query text in the editor with whatever it was prior to processing. fix-SQL Editor will edit the original query in place adding concatenation and fixing any formatting issues.
fix-SQL Editor uses function keys extensively rather than key combinations because they are more accessible and faster than key combinations. They're also easier on your hands when you're converting a lot of code. This list doesn't contain the usual document shortcuts for Cut, Copy etc which are avaiable when using the Editor and the Results pane.
F5
Short cut to the Run button which processes a query.F8
Used to select text which processing must ignore. For example, a helper class function might have a database connection variable in it's method signature. To make sure this isn't interpreted by processing as a query variable which would fail processing, the F8 key will mark this to be ignored. In operation you select the text in the Editor you want to exclude from processing and then press F8.F9
In manual processing you select column \ Value pairs for processing. This key will select the column. The value is selcted by the F10 key.F10
Maunual processing shortcut to select a value for a column\Value pair. You select the column with the F9 key then select the selected Value with the F10 key at which point the value input is edited in place in the query to the named parameter. When you select a value for a pair you need to select any concatenation at each end of the Value. Double clicking a value will do this automatically however some multi part Values will need to be manually selected with enclosing characters.F11
Selects the table for a value pair in manual processing. Rarely used because fix-SQL Editor will accurately identify the table for a pair you select.Insert Statements
If you're processing an Insert statement there is a known issue when a variable is used for a column and the statement you're writing doesn't use VALUES(.. The older version of an Insert statement will leave the column variable unparameterised.
Non SQL Code Error
The most common error is caused by non SQL code which fix-SQL Editor has assumed forms part of the SQL text. If the SQL escape character is used this doesn't happen but if it isn't then occassionaly fix-SQL Editor will add non SQL code to query text and this will fail in processing. Following is an example of a VB.net Method which fails in this way. Non SQL code which precedes a statement is rarely an issue, the non SQL following the Query text can occassionally be a problem.
Here is the pasted Method which will fail.
A fix-SQL Editor feature which allows for variables to be written into a query for processing without concatenation means that a failure to identify the end point of the query text will see non SQL code being concatenated as if they are part of the query.
The fix is very simple. Just select the offending code and press F8. It will look like this:
Now when you process this query it will succeed.
Permanently Excluding recurring Non SQL text from Processing
Sql Helper classes can often have inputs in Method Signatures which when used in a sql call may cause issues for fix-SQL Editor. If this happens then simply add the Non Sql word you want excluded into the Exclusions List accessed from the Project Set Up Menu.
Finding Syntax Errors in Source Code
Mystery errors can be often be solved by using the SQL Parser button. Select the text (the pure SQL text only) and then click the Sql Parser button. Great way to locate a missing comma or other invalid syntax in the query.
Select just the pure SQL text in your query. For example if this was a SELECT query you would select text from SELECT or DECLARE to the end of the statement. fix-SQL Editor will parse the text against the connected Sql Server without actually running the query so that Sql Server will only report any syntax errors it has found.
fix-SQL Editor installed is about 30 mb. Installs are automated so you won't see any dialogues, it just installs. The destination folder is APPDATA\fixSQLnet. fix-SQL Editor will identify the availability of maintenance releases and inform the user. After permission to install a maintenance release this will run silently and be applied the next time the user launches fix-SQL Editor. To install fix-SQL Editor just go to the Download button on the main fix-SQL Editor page. This will download fixSqlEditInstaller.exe. The file is signed by REPLICA DATA SECURITY with a valid crtificate from Sectigo(Comodo). Launch the fixSqlEditInstaller.exe (to avoid windows warnings right click the installer exe go to Properties, General and select UnBlock), the install will begin silently, no dialogues, a desktop shortcut will appear on the desktop and fix-SQL Editor will launch with a Project Set up menu to get started. The install takes about 2 to 5 seconds or so. The install will behave as any Windows installed App does, listed for uninstall in the usual manner and accessed through the shortcut and Start Menu. There is also a fixSqlEditInstaller.msi file if you prefer to use it. After an update you will see in the application folder inside APPDATA (APPDATA\fixSQLnet) that you have multiple folders for fix-SQL Editor. Don't worry about this the system will clean up old versions from the install directory over time keeping the footprint for fix-SQL Editor small. This is a Windows Application tested for Windows 10 Framework 4.61. This version of fix-SQL Editor runs against a SQL Server local or remote or Sql Server on Azure.