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 fixSQL 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 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='fixSQL',dbconnection)"). When this is tokenised dbconection follows a comma and can be assumed incorrectly, to form part of the SQL text. This will fail fixSQL 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 parameterised query when it's used.
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 you remove a step in the transaction on Sql Server and this helps performance. Autocomplete operates on Schema name when its (fixSQL knows what tables are in each Schema) used and when you place a period it will show all tables belonging to that Schema. Most large databases will use multiple Schema's.'
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. Of all the Sql Injection defensive strategies, parameterisation is the most tedious, error prone, labour intensive and effective. It takes a toll on developers physically , mentally and emotionally and costs companies significant expenditure. Parameterising Legacy 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 fixSQL is but from experience a large system can be completed in a month instead of a year. There is no system faster than fixSQL, 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 parameterised. 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 fixSQL project wizard there is a flag which will force fixSQL 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 this. You might also consider always using the NameSpace (schema name) in queries as this also improves performance.
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 schema edits a potential problem because changes in data type etc need to be edited in the type safe parameters. A flag in fixSQL 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 type safe descriptor. When you are also looking at edits on the source code during the process fixSQL can help out. Just click the Load button at the bottom of the 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 legacy 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. fixSQL can avoid rewrites of legacy applications which need to be secured. The processs is massively faster and simpler with fixSQL 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 fixSQL anytime you autocomplete 'JOIN' (of course you must have added a table with FROM first in the text) fixSQL 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 thast table in the join will use the aliase. When you would like to add an aliase to the joined table you can do this by adding the table and aliase into the Tables and Aliases treeview. So if you added Customers C into the list when you type JOIN you will see all joins onto the customer table will begin with JOIN Customers C on .. and then all Customer table refrences will be C.[joinedColumn].
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 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 alise represents. immediately. An aliase in the query will be loaded and autocomplete without any additoinal 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 will edit the original query in place adding concatenation and fixing any formatting issues.
fix-SQL 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 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 fixSQL 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 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 fixSQL 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 fixSQL. 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 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.
fixSQL installed is about 20 mb. Installs are automated so you won't see any dialogues, it just installs. The destination folder is APPDATA\fixSQLnet. fixSQL 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 fixSQL. To install fixSQL just go to the Download button on the main fixSQL page. This will download Setup.exe, Google will complain about this file type with warnings but will still download the installer to avoid warnings you can use Edge or other browser. The file is signed by REPLICA DATA SECURITY. In the extremely unlikely event that the signature isn't there or the certificate won't validate, don't launch the exe, Please contact Support (don't be alarmed this has never happened it's just a reminder to validate the certificate for an installer for safety). Launch the Setup.exe, the install will begin silently, no dialogues, a desktop shortcut will appear on the desktop and you will know that the install has completed. The install takes about 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 Setup.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 fixSQL. Don't worry about this the system will clean up old versions from the install directory over time keeping the footprint for fixSQL small. This is a Windows Application tested for Windows 10 Framework 4.61. This version of fixSQL runs against a SQL Server local or remote or Sql Server on Azure.