Names with apostrophes

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a web form that is connected to a database. I'm collecting user
information and when a user with a name like O'reilley enters his name it
fails because of the apostrophe. How do I detect the apostrophe and pass
the insert command a safe string to insert it into the database. Is there
an existing function that will do this for me? What is the best way to
handle this? Another example is the middle initial. If the user enters Tom
L. Smith. the period after the middle initial crashes the app as well

Thanks in advance,

Jason
 
The safest way is to use stored procedures.
ADO handles these apostrophes etc for you and users cannot hack your system
with code injection.
 
Hi Jason,

You might consider using parametrised sql commands (doesn't matter if normal
sql sentences or stored procedures).
 
Jason said:
I have a web form that is connected to a database. I'm collecting user
information and when a user with a name like O'reilley enters his name it
fails because of the apostrophe. How do I detect the apostrophe and pass
the insert command a safe string to insert it into the database. Is there
an existing function that will do this for me? What is the best way to
handle this? Another example is the middle initial. If the user enters Tom
L. Smith. the period after the middle initial crashes the app as well
Click to expand...

Use a command with parameters, rather than inserting the value directly
into your SQL.
 
The advice to use SPs or parameterized SQL is the best.
But if you just want to handle the apostrophe try doubling it up.

O'Reilly becomes O''Reilly.

Use the replace command.

OTOMH:
strName = txtName.Text.Replace("'","''")
That is a single quote being replaced by 2 single quotes.
 
This is covered in the knowledge base article #311023
http://support.microsoft.com/default.aspx?scid=kb;EN-US;31102

However, I am using an SQLDataAdapter to automatically generate update SQL from a DataSet updated from a DataGrid control. A single tick in a string field causes the SQL to bomb, double ticks stores double ticks to the database table. I suspect this might be an ADO.NET bug (I'm using .NET 1.0), but surely I can't be the first one to encounter this

Mike
 
Mike Murphy said:
This is covered in the knowledge base article #311023:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311023

However, I am using an SQLDataAdapter to automatically generate
update SQL from a DataSet updated from a DataGrid control. A single
tick in a string field causes the SQL to bomb, double ticks stores
double ticks to the database table. I suspect this might be an
ADO.NET bug (I'm using .NET 1.0), but surely I can't be the first one
to encounter this.
Click to expand...

As others have said, the solution isn't to mangle the SQL - it's to
avoid putting the value into the SQL to start with. I personally steer
clear of automatically generated SQL commands to start with - I reckon
by the time I've convinced myself that the generated command is
correct, I might as well have written it by hand and made it that much
more explicit to the reader.
 
Back
Top