locked
MS ACCESS Insert Query Failure RRS feed

  • Question

  • User2098908334 posted

    Im in great trouble. please help me.
    Im using MS ACCESS DataBase. My database stores the URL of websites. Im inserting the URL into my database as follows :

    string sql = "insert into url_tab values('" + textbox1.text + "','" + textbox2.text + "')";
    OleDbCommand od = new OleDbCommand(sql, oc); // oc=connection object
    od.ExecuteNonQuery();

    //textbox1 -> reads URL
    //textbox2 -> reads password

    where my problem arises is, when a user types-in password that contains a single-quotes; then an excepetion is thrown due

    to the presence of single quotes. Also the typed-in URL contains back-slah (\\) and dotes(.) ; which will also throws an

    exception.
    But if im entering text without special character, the insertion happens nicely.

    Is there any way to insert values into the table other than the method mentioned above, so that i can insert the value into

    table smoothly without any exception.

    However, i can solve this problem by using stored-procedure in SQL Server. But im forced to stick on to ACCESS.
    Or else, if i want using SQL Server, what all things i've to install on the end-users machine, at the time of deployment.

    Because, if we are using MS ACCESS, it is not necessary that the end-user should install MS ACCESS. I've SQL 2005 with me.

    Please do help me.
    Tankz.

    Sunday, April 20, 2008 5:41 AM

Answers

  • User-1063239682 posted

    What you are doing is very bad, security wise, your approach to sql queries will be vunlerable to attacks called "Sql Injection". This particule query is not a big threat for MS Access, but if you write a query like "select * from users where username = '" + TextBox.Value "' and password = '" + TextBox2.Value then if someone writes something like this in your password field

    " ' or 1 = 1 or 'asd" (without the double quotes) then he will gain access to unauthorized accounts.

    Please always use parameterized queries, in which you add parameters in your sql query (not sure for Access how this is done) maybe something like this

    insert into table values(?param1, ?param2)

    And then you add the paramter values to the sql command object directly. That would be safe.

    Have secure coding, please lookin Sql Injection on the internet and get more examples.

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 20, 2008 6:11 AM

All replies

  • User-1063239682 posted

    What you are doing is very bad, security wise, your approach to sql queries will be vunlerable to attacks called "Sql Injection". This particule query is not a big threat for MS Access, but if you write a query like "select * from users where username = '" + TextBox.Value "' and password = '" + TextBox2.Value then if someone writes something like this in your password field

    " ' or 1 = 1 or 'asd" (without the double quotes) then he will gain access to unauthorized accounts.

    Please always use parameterized queries, in which you add parameters in your sql query (not sure for Access how this is done) maybe something like this

    insert into table values(?param1, ?param2)

    And then you add the paramter values to the sql command object directly. That would be safe.

    Have secure coding, please lookin Sql Injection on the internet and get more examples.

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 20, 2008 6:11 AM
  • User2098908334 posted

    Thank you Ralph, actually ive developed the application in SQL Server2005. But i was forced to change to MS ACCESS. thats y all this problem aroused.

     

    But ive one more question in mind, if im developing using sql 2005, how should i deploy the project for end-user . There is an option to add 'Local Database' in 'Add New Item' of Visual Studio. If im developing my applcn using this mdf file, can i deploy the application to my end-user without any sql server installed in his machine? will that work?

    Sunday, April 20, 2008 8:21 AM
  • User-821857111 posted

    But ive one more question in mind, if im developing using sql 2005, how should i deploy the project for end-user .

     

    A Sql Server database needs Sql Server to work.  If it's not installed, it won't work.  Access is a file-based database.  Actually, it's not even an Access database you are currently using - it's a Jet mbd database.  That's why you don't need Access installed.

    Here's how to use parameters with Access/Jet, by the way: http://www.mikesdotnetting.com/Article.aspx?ArticleID=26

     

    Sunday, April 20, 2008 2:34 PM
  • User-1063239682 posted

    For creating your database, I think the best way for sql server is by having your application run a script. The script is used to create the database.

    When your application starts, it checks for a certain table, if it does not exist then your database is not created yet. Create it by running a script directly to the database. The way I do it is that I use a modeling tool, I model my database in it and then the tool creates the database script for me, I do not create the tables directly in SQL SERVER, However you can have Sql Server generate scripts for you that would create your database if your tables are already there.

    Recently I have installed Subtext (a personal blogging system) and subtext used that method as well.

    Hope this helps, let me know if you need further help.

    Sunday, April 20, 2008 4:22 PM
  • User2098908334 posted

    Thank you all for ur valuable response.

    i solved my problem

    dnx , a lot...

    Monday, April 21, 2008 3:57 AM
  • User-1063239682 posted

    If you still need anything, let me know.

    Monday, April 21, 2008 5:40 AM