Click here to Skip to main content
14,327,607 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am running oracle query and getting the data storing it in dataset(dsDataSet)
STEP 2 :Copying it to datatable.
dtAEDetails= dsDataSet.Tables[0].Copy();)
Step 3 : storing the datatable into access database.

I am having issue in datatable, one field Desc1 in datatable having single quote(ex:
'SCHOLAR'S EDGE THE AGGR P'


The below code is inserting datatable into access database.
if datatable rows >0 then inserting into access database.
DESC1 field is having single quote , how to replace single quote with double quote before insert into database.
if (dtAEDetails.Rows.Count > 0)
               {
                   //Set up the params
                   foreach (DataRow drAEDetails in dtAEDetails.Rows)
                   {
                       sInsertValues =
                            "'" + drAEDetails["OFFICE"].ToString() + "','"
                                + drAEDetails["AE"].ToString() + "','"<pre> if (dtAEDetails.Rows.Count > 0)
               {
                   //Set up the params
                   foreach (DataRow drAEDetails in dtAEDetails.Rows)
                   {
                       sInsertValues =
                            "'" + drAEDetails["OFFICE"].ToString() + "','"
                                + drAEDetails["AE"].ToString() + "','"
                                + drAEDetails["SETDATE"].ToString() + "','"
                                + drAEDetails["ACCT"].ToString() + "','"
                                + drAEDetails["SHORT"].ToString() + "','"
                                + drAEDetails["BS"].ToString() + "','"
                                + drAEDetails["SHARES"].ToString() + "','"
                                + drAEDetails["DESC1"].ToString()+ "','"
                                + drAEDetails["PRICE"].ToString() + "','"
                                + drAEDetails["PRINC"].ToString() + "','"
                                + drAEDetails["GROSS"].ToString() + "','"
                                + drAEDetails["STAND"].ToString()  + "','"
                                + drAEDetails["NET"].ToString()  + "','"
                                + drAEDetails["NEWNET"].ToString() + "','"
                                + drAEDetails["AECHG"].ToString()+ "','"
                                + drAEDetails["BONUS"].ToString()+ "','"
                                + drAEDetails["BT"].ToString() + "','"
                                + drAEDetails["CANCEL"].ToString() + "','"
                                + drAEDetails["TRANSCODE"].ToString() + "','"
                                + drAEDetails["ORDERSORT"].ToString() + "','"
                                + drAEDetails["REALGR"].ToString() + "','"
                                + drAEDetails["ANNUITYNET"].ToString() + "','"
                                + drAEDetails["RECORDID"].ToString() + "','"
                                + drAEDetails["USER_CODE_1"].ToString() + "','"
                                + drAEDetails["USER_CODE_7"].ToString() + "'";


                       insertAEdetails.ParmValue = sInsertValues;
                       insertAEdetails.InsertIntoAccess();

how to replace single quote into double quote in desc field and store it to dataset, after replacing I want to store it into datatable . how to do it.

Please guide me.

Thanks

What I have tried:

I thought of replacing in query , but my colegue said it's easy in code.

can you please give me idea.
Posted
Updated 29-Aug-17 11:45am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

You don't replace the single quote. You change your database handling code, which is probably written use string concatenation, to using parameterized queries instead.

Google for "SQL Injection Attack" to find out why what you're doing is so bad. Then Google for "C# parameterize query" to find out how to fix it.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

The problem is that the way you do your queries is all wrong - and very dangerous.
Never concatenate strings to form SQL commands: it leaves you wide open to something called SQL Injection, which can damage, destroy, or hand over your database to the user, or allow him to bypass your login requirements at will. And it causes problems like this as well...
Always use parameterized queries instead, it's the only way to be safe.

And the first thing you need to do is fix every other query in you Application: leave just one in there, and you better make sure you have good, solid, frequent backups of your DB, because you are going to be restoring it often...
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

Your problem is the very popular SQL Injection problem.

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100