By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,980 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,980 IT Pros & Developers. It's quick & easy.

Insert text contain code

P: n/a
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
Nov 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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\";")

Nov 19 '05 #2

P: n/a
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\";")

Nov 19 '05 #3

P: n/a
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\";")


Nov 19 '05 #4

P: n/a
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\";")


Nov 19 '05 #5

P: n/a
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\";")



Nov 19 '05 #6

P: n/a
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\";")
>



Nov 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.