Lelle. You're using so called "dynamic sql" which you construct right from
your code. Although it works just fine for small projects, this approach has
several issues, from security to performance to manageability. By saying
"parameters" we mean using stored procedures or parameterized statements. In
short, the process is fairly simple:
1. Create a stored procedure that would select whatever data your page
needs; most definitely it would have some parameters (say, if you select
some orders for specific dates, then parameters would be those dates and
customerID); check out SqlServer's Help to learn all about them, if you need
to (in Query Analyzer click Help / Transact-SQL Help option);
2. You use DataAdapter to fill a dataset; Microsoft has released a good
collection of classes called "Application Blocks"; it's free, just search
msdn.microsoft.com to find and download it; it includes documentation, too,
I believe;
3. Or use your own class; it's also not difficult to create one; the best
thing would be to compile such classes as a separate project, so you can
reuse it across multiple applications. Below is the sample of data access
method of such class (sorry, I use C# :)):
public static DataSet ExecSPDataSet(SqlConnection conn,int
commandTimeout,string procName,params object[] arrayOfParams)
{
try
{
SqlCommand comm = new SqlCommand(procName,conn); // create new Command
object
comm.CommandType = CommandType.StoredProcedure; // tell the code that you
gonna use stored procedure
SqlCommandBuilder.DeriveParameters(comm); //go to the Sql Server to get
names and data types of sproc parameters
comm.CommandTimeout = commandTimeout; // set timeout, useful if you have a
large chuncks of data to be returned
for( int i=1; i < comm.Parameters.Count && i < arrayOfParams.Length+1; i++ )
comm.Parameters[i].Value = arrayOfParams[i-1]; // "transform" your array of
parameters into Command.Parameters object
SqlDataAdapter da = new SqlDataAdapter(comm); // create a new instance of
SqlDataAdapter object
DataSet ds = new DataSet(); // ...and new dataset which will contain your
data, too
da.Fill(ds); // physically go to get your data and fill dataset
comm.Parameters.Clear(); // just in case if you'll reuse this Command again
if(conn.State == ConnectionState.Open)
conn.Close(); // no matter what books say about pooling and garbage
collection, always close your connection
return ds; // enjoy your data
}
catch(SqlException ex)
{
if(ex.Number == 1205) //check for deadlocks, it would be MUCH better to
start a timer for the random number of seconds, so other requests would not
cause an infinitive loop
return ExecSPDataSet(conn,commandTimeout,procName,arrayOf Params); // call
this method again if deadlock happened
else throw; // something else happened - let the rest of the code know about
it
}
}
As you can see, this method calls stored procedure and expects an array of
objects, which are your parameters (key word "params" has nothing to do with
sql, it tells C# that those methods are not required).
Hope this helps a bit :)
Kikoz.
"Lelle" <sv****@hotmail.com> wrote in message
news:42********@news.wineasy.se...
thanx guys ... im kinda newbie to this and im not sure what you mean with
parameters. Can u give me an example?
Dim Solution textbox1.text as string and then i set in my sql string
like this
Me.DL.InsertData("INSERT INTO data (subject omrade, solution, kategori)
VALUES ('" & Me.tbsubject.Text & "','" & Me.ddlOmrade.SelectedItem.Text &
"','" & solution & "','" & Me.ddlKat1.SelectedItem.Text & "')")
This works good for oridinary text
"Kikoz" <ki***@hotmail.com> wrote in message
news:u3**************@TK2MSFTNGP12.phx.gbl... Matt is right - parameters is the solution. But if it's not possible,
encrypt the text before sending to db and decrypt it before
displaying/using when requesting it from db.
Regards,
Kikoz.
"Matt Berther" <mb******@hotmail.com> wrote in message
news:22***************************@news.microsoft. com... Hello Lelle,
Why dont you use parameters? This would handle any issues around sql
injection.
--
Matt Berther
http://www.mattberther.com
Yes i understand that and i can insert som code but for example i can
not insert this
- text in the textbox that contains the data to insert
If e.Item.ItemType <> ListItemType.Header And e.Item.ItemType <>
ListItemType.Footer Then
e.Item.Cells(2).Attributes("onclick") = "javascript:return
confirm('Are you sure u wanna delete? \n " & _
DataBinder.Eval(e.Item.DataItem, "Subject") & "')"
End If
because the ' signs the command gets misiterpreted, i could make a
replace ( as i do when i wanna make sure this signs dont exist in my
SQL statements injection problem) but then i cant copy and pase the
code at later time so in this particular case it would be nice to use
the illegal chars...
"ech0" <th******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
> well you wanna escape your string quotes so it doesn't get
> misinterpretted. everything else should be ok, just make sure you
> escape your string quotes.
>
> INSERT INTO table_name VALUES (1, "string x = \"test\";")
>
