The regular expression extracts the @ Parameter Name of the SQL statement and fixes a bug in subsonic.

Subsonic inlinequery is very easy to use. It is used to execute SQL statements of parameters to prevent injection. Its Running Mechanism is similar to the borrow statement I wrote earlier. net Framework string. fromat (...), to implement a method for executing parameterized SQL statements, the command is first created based on the parameter name in the SQL statement, and then dynamically constructed based on the parameter name and input value.

Paste an official example of using inlinequery:

Northwind. productcollection Products =
New Inlinequery ()
. Executeascollection < Northwind. productcollection >
( " Select productid from products where productid = @ productid " , 1 );

 

 

When I use inlinequery to execute an SQL statement with parameters, I found a bug in subsonic's analysis of SQL parameters and tracked it into subsonic'sSource codeIt is found that the parameters for parsing SQL statements are parsed as follows:

  private   static  List  string  parseparameters ( string  SQL) {list  string  result =  New  List  string  (); regEx paramreg =  New  RegEx (@ " @ \ W * "); matchcollection matches = paramreg. matches (string. concat (SQL, " ");  foreach  (Match m  in  matches) result. add (M. value);  return  result ;}

See the third line, RegEx paramreg =NewRegEx (@"@ \ W *"), Its other mechanism is to use regular expressions for grouping and matching, for example, the following SQL statement:
 

 
Select * from students where fname = @ name; select @ rowcount
 

 
Variables such as @ rowcount are extracted during extraction,
 

Obviously this is not what we want. The result we want is to extract only the @ name parameter name, which obviously cannot meet the requirements, actually, the expression @ "@ \ W *" is too hasty to handle. Fortunately, the source code of open-source products is well modified, and it would take 10 minutes (it's really embarrassing, I haven't used this for a long time.) Write a regular expression: ([^ @] (? <P> @ \ W +) | (? <P> ^ @ \ W +)

Last ModifiedCodeAs follows:

 Private   Static List < String > Parseparameters ( String SQL) {list < String > Result =New List < String > (); // RegEx paramreg = new RegEx (@ "@ \ W *");  // When the regular expression matches a parameter, the SQL statement contains a variable such as @ rowcount, which should not be counted as a parameter. RegEx paramreg = New RegEx (@" [^ @] (? <P> @ \ W +) "); Matchcollection matches = paramreg. Matches (string. Concat (SQL ,"   ")); Foreach (Match m In Matches) result. Add (M. Groups [" P "]. Value ); Return Result ;}

 
If you still have a simple method, please leave a message to inform me. Thank you.