Insert text contain code

[Lelle]

Hello !

how can i insert text containg code examples from a textbox into a database
using SQL insert statment.

i have no problem to just add text that dont contains code and script
examples or the illegal chars for the insert command

is it possible to encasulate the text/string so the server doesnt reads the
string as a command?

i guess this is something with sql injections and regular expressions

Thanx

[ech0]

Re: Insert text contain code

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\";")

[Lelle]

Re: Insert text contain code

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.He ader And e.Item.ItemType <>
ListItemType.Fo oter Then
e.Item.Cells(2) .Attributes("on click") = "javascript:ret urn
confirm('Are you sure u wanna delete? \n " & _
DataBinder.Eval (e.Item.DataIte m, "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" <the.ech0@gmail .com> wrote in message
news:1111952982 .792141.237970@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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\";")
>[/color]

[Matt Berther]

Re: Insert text contain code

Hello Lelle,

Why dont you use parameters? This would handle any issues around sql injection.

--
Matt Berther

http://www.mattberther.com

[color=blue]
> 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.He ader And e.Item.ItemType <>
> ListItemType.Fo oter Then
> e.Item.Cells(2) .Attributes("on click") = "javascript:ret urn
> confirm('Are you sure u wanna delete? \n " & _
> DataBinder.Eval (e.Item.DataIte m, "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" <the.ech0@gmail .com> wrote in message
> news:1111952982 .792141.237970@ z14g2000cwz.goo glegroups.com.. .
>[color=green]
>> 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\";")
>>[/color][/color]

[Kikoz]

Re: Insert text contain code

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" <mberther@hotma il.com> wrote in message
news:221a09c78d d6868c700fe75ba d5b1@news.micro soft.com...[color=blue]
> Hello Lelle,
>
> Why dont you use parameters? This would handle any issues around sql
> injection.
>
> --
> Matt Berther
> http://www.mattberther.com
>[color=green]
>> 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.He ader And e.Item.ItemType <>
>> ListItemType.Fo oter Then
>> e.Item.Cells(2) .Attributes("on click") = "javascript:ret urn
>> confirm('Are you sure u wanna delete? \n " & _
>> DataBinder.Eval (e.Item.DataIte m, "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" <the.ech0@gmail .com> wrote in message
>> news:1111952982 .792141.237970@ z14g2000cwz.goo glegroups.com.. .
>>[color=darkred]
>>> 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\";")
>>>[/color][/color]
>
>
>[/color]

[Lelle]

Re: Insert text contain code

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.InsertDat a("INSERT INTO data (subject omrade, solution, kategori)
VALUES ('" & Me.tbsubject.Te xt & "','" & Me.ddlOmrade.Se lectedItem.Text &
"','" & solution & "','" & Me.ddlKat1.Sele ctedItem.Text & "')")

This works good for oridinary text


"Kikoz" <kikoz@hotmail. com> wrote in message
news:u39qrH0MFH A.3228@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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" <mberther@hotma il.com> wrote in message
> news:221a09c78d d6868c700fe75ba d5b1@news.micro soft.com...[color=green]
>> Hello Lelle,
>>
>> Why dont you use parameters? This would handle any issues around sql
>> injection.
>>
>> --
>> Matt Berther
>> http://www.mattberther.com
>>[color=darkred]
>>> 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.He ader And e.Item.ItemType <>
>>> ListItemType.Fo oter Then
>>> e.Item.Cells(2) .Attributes("on click") = "javascript:ret urn
>>> confirm('Are you sure u wanna delete? \n " & _
>>> DataBinder.Eval (e.Item.DataIte m, "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" <the.ech0@gmail .com> wrote in message
>>> news:1111952982 .792141.237970@ z14g2000cwz.goo glegroups.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\";")
>>>>[/color]
>>
>>
>>[/color]
>
>[/color]

[Kikoz]

Re: Insert text contain code

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 "Applicatio n 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(S qlConnection conn,int
commandTimeout, string procName,params object[] arrayOfParams)

{

try

{

SqlCommand comm = new SqlCommand(proc Name,conn); // create new Command
object

comm.CommandTyp e = CommandType.Sto redProcedure; // tell the code that you
gonna use stored procedure

SqlCommandBuild er.DeriveParame ters(comm); //go to the Sql Server to get
names and data types of sproc parameters

comm.CommandTim eout = 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.L ength+1; i++ )

comm.Parameters[i].Value = arrayOfParams[i-1]; // "transform" your array of
parameters into Command.Paramet ers 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(SqlExcept ion 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(c onn,commandTime out,procName,ar rayOfParams); // 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" <svslen@hotmail .com> wrote in message
news:4247b236$1 @news.wineasy.s e...[color=blue]
> 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.InsertDat a("INSERT INTO data (subject omrade, solution, kategori)
> VALUES ('" & Me.tbsubject.Te xt & "','" & Me.ddlOmrade.Se lectedItem.Text &
> "','" & solution & "','" & Me.ddlKat1.Sele ctedItem.Text & "')")
>
> This works good for oridinary text
>
>
> "Kikoz" <kikoz@hotmail. com> wrote in message
> news:u39qrH0MFH A.3228@TK2MSFTN GP12.phx.gbl...[color=green]
>> 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" <mberther@hotma il.com> wrote in message
>> news:221a09c78d d6868c700fe75ba d5b1@news.micro soft.com...[color=darkred]
>>> 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.He ader And e.Item.ItemType <>
>>>> ListItemType.Fo oter Then
>>>> e.Item.Cells(2) .Attributes("on click") = "javascript:ret urn
>>>> confirm('Are you sure u wanna delete? \n " & _
>>>> DataBinder.Eval (e.Item.DataIte m, "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" <the.ech0@gmail .com> wrote in message
>>>> news:1111952982 .792141.237970@ z14g2000cwz.goo glegroups.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\";")
>>>>>
>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]