Error saying:Incorrect syntax near the keyword 'And'

[Maximus007]

I have been trying to figure this out for quite some time. Any help is greatly appreciated.

error:Incorrect syntax near the keyword 'And'


Code:

Dim strAddSQL As String

strAddSQL = ("Update Servers set ServerName='" & txtServerName.Text & "' And DateRegistered='" & Date.Today & "'")
Response.Write(strAddSQL)
Response.End()

Dim strAddCmd As SqlCommand

strAddCmd = New SqlCommand(strAddSQL, strAddConn)


strAddConn.Open()

strAddCmd.ExecuteNonQuery()
strAddConn.Close()

 

[ca8msm]

Use a comma rather than "And"


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8msm]

Oh, and use parameters as well otherwise someone could really screw up your database with a simple SQL injection.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Maximus007]

Thank you!

 

[vbkris]

>>Oh, and use parameters as well otherwise someone could really screw up your database with a simple SQL injection.

i just did a search on google for that and the first link that i came up was with this:

http://www.securiteam.com/securityreviews/5DP0N1P76E.html

8.0 How to avoid SQL Injection?
Filter out character like single quote, double quote, slash, back slash, semi colon, extended character like NULL, carry return, new line, etc, in all strings from:
 - Input from users
 - Parameters from URL
 - Values from cookie

now lets take a scenario:
--> ALL my forms are protected with ServerControls where i allow only certain characters.
--> I enclose all my values using '' (including numbers)
--> Have a HTTP module that checks all the values submitted

now is it safe to use just a plain SQL?

what i have against Parameters:
--> extra code
--> extra load on the server (however small)

Known is handfull, Unknown is worldfull

 

[ca8msm]

It's quite probably safe if you've removed every character that you don't want but (and a few of these yopu've already highlighted):

1) It's slower
2) It uses more resources
3) It's more work
4) It's harder to maintain
5) Parameters would actually allow these characters to be safely entered into the db (whereas I presume you just remove them?)

Personally, I don't see the point in it but if you've already implemented it then you're probably OK.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

>> 1) It's slower
how? i deal with everything in just on string(or stringbuilder). i dont have any extra parameter objects.

>> 2) It uses more resources
once again how? from what point of view does it use more resource?

>> 3) It's more work
Thats a point of view, i feel that typing it in there itself is better (but its my personal view), again sicne i dont use params my work is reduced, on the other hand if u have params then there is more work, like creating a param object for each parameter, set a type etc...

>> 4) It's harder to maintain
again why? from what point of view is a stmt like:

this "Update Servers set ServerName='" & txtServerName.Text & "' And DateRegistered='" & Date.Today & "'"

diffuclt to maitain?


Known is handfull, Unknown is worldfull

 

[ca8msm]

These should have all be fairly obvious but here goes:

>> 1) It's slower
how? i deal with everything in just on string(or stringbuilder). i dont have any extra parameter objects.

Because you have to call your function for every value that you want to insert into the database whereas with parameters you just add the value.

>> 2) It uses more resources
once again how? from what point of view does it use more resource?

Again, you have to call your function every time and therefore that puts a bigger load on your server.

>> 3) It's more work
Thats a point of view, i feel that typing it in there itself is better (but its my personal view), again sicne i dont use params my work is reduced, on the other hand if u have params then there is more work, like creating a param object for each parameter, set a type etc...

Imagine the poster hasn't done the work you've done (or the equivilant work that has already been done in microsoft's freely downloadable data access layer that allows you to add parameters in one line). Which is more work:

1) Adding a parameter
2) Creating a function to strip out every character

>> 4) It's harder to maintain
again why? from what point of view is a stmt like:

this "Update Servers set ServerName='" & txtServerName.Text & "' And DateRegistered='" & Date.Today & "'"

diffuclt to maitain?

What happens if you decide you suddenly want to allow single quotes in this statement but not in all your others? You'll have to modify your function to allow them but then it would allow them for all statements so you'd have to either change the sql statement or create two functions. If you were using parameters you wouldn't have to do anything...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

function?
the sql will be written by the application developer himself, the DAL function will take the SQL string as a parameter.

therefore:
>>Imagine the poster hasn't done the work you've done

this doesnt apply, my DAL simply takes a SQL string as parameter. the building is left to the induvidual programmer. this is where the time is saved (he has to just build the SQL without any parameters).

the other checks are in a Module, therefore enforcing uniformity.

>>What happens if you decide you suddenly want to allow single quotes in this statement but not in all your others?

good point, but i ALLOW single qoutes in my forms (like a descrption field where validation cannot be done), my Module simply replaces them with double single qoutes...

Known is handfull, Unknown is worldfull

 

[ca8msm]

function?
the sql will be written by the application developer himself, the DAL function will take the SQL string as a parameter.

I mean the function that does "where i allow only certain characters". Whether that's in your module or whatever is irrelevant. It still has to be called to either strip out or "double up" whatever characters you are talking about for every single value you want to insert. This step simply doesn't exist with parameters.

>>Imagine the poster hasn't done the work you've done

this doesnt apply, my DAL simply takes a SQL string as parameter. the building is left to the induvidual programmer. this is where the time is saved (he has to just build the SQL without any parameters).

It does apply. The poster who posted this question and any future readers probably haven't done any work on this and are looking for the safest and quickest method.

>>What happens if you decide you suddenly want to allow single quotes in this statement but not in all your others?

good point, but i ALLOW single qoutes in my forms (like a descrption field where validation cannot be done), my Module simply replaces them with double single qoutes...

Yes, but you've had to write the code in the first place to double them up to make them be allowed. With a parameter you wouldn't have had to do anything.

Honestly, and I'm not just saying this to cause an argument because you are perfectly entitled to have your say and opinion on the matter, but I really think you are wrong and are overcomplicating a very simple issue.

If you want to go into this in more detail, start a new thread and we'll go through the exact steps that both you and I would do to insert one value into a table. We'll then see which version is easier and more maintainable.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[vbkris]

sure, will do that. just give me sometime to post up with the code...

Known is handfull, Unknown is worldfull

 

[ca8msm]

OK


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.