problem inserting in database

i have a page writing to a dsatabase. this is the string

<code>

cmd = new sqlcommand("insert into company_locations (company_id, location_type, location_name, contact, address, suite, address2, city, state, zip, phone, extension, fax, comments) Values ('" & provider_id & "', '" & location_type.selectedvalue & "', '" & location_name.text & "', '" & contact.text & "', '" & address.text & "', '" & suite.text & "', '" & address2.text & "', '" & city.text & "', '" & states.selecteditem.value & "', '" & zip.text & "', '" & phone.text & "', '" & extension.text & "', '" & fax.text & "', '" & comments.text & "')", xDb_Conn_Str)

</code>

the problem is that in location_name, I am allowing ' as a valid character. Can anyone help me with this. everytime i execute my statement is truncated because of '.

thanks

Your suffering from a problem that is only slightly less serious than the one that you will face in production: your code is liable to SQL injection attacks, assuming that the input is coming from the user.

Consider what would happen if the entry for, say, contact.text contained:

joe' ); DROP TABLE company_locations; --

Given the right level of privilege, your company_locations table would disappear. Note that this is probably the least of your worries - hackers would start attempting to run command line code on you SQL Server via the built in stored procedure for executing shell code.

You should use parameterised queries to overcome both your problem and to help mitigate against SQL injection attacks. Thus you should be using

... Values( @.ProviderId, @.LocationType, etc. )

for the command text, and then add SqlParameter objects for each parameter

eg

cmd.Parameters.Add( "@.ProviderId", provider_id )

(Note that there are lots of overloads for creating parameters, or you can create the SqlParameter object separately and then add it to the Parameters collection).|||I had understood the problem with sql injectioons. I had already implemented validation string to ensure that certain characters would not be allowed for those exact reason. It was just the ' that I was needing to add. Thanks for the advice you gave me though, it will help. I did not realize that by passing my text as a parameter that I could include the apostrophe. Thanks for the help.|||

msamford wrote:

I had already implemented validation string to ensure thatcertain characters would not be allowed for those exact reason.

This approach (filtering out bad input) is called "blacklisting" and isnot at all recommended. "Whitelisting" (letting in only expectedinput) is much preferred. But parameters are the mostpreferredapproach of all -- you should always use parameters.