Updating an mdb database table

Question

I'm building a simple site using .NET Web Forms and a .mdb database as the data source.

The problem is: I have a working backsite trough which I can create, modify and delete a new page.

While I can create and delete, the editing feature doesn't work.

I realized the problem might be in the query since the code is working fine for the other features.

Here is the query:

UPDATE pages 
SET title=\"" + pa.title + "\" content =\"" + pa.content + "\" 
WHERE id=" + pa.id

I'm not very fond of SQL, am I doing something wrong?

Thank you in advance

Edit N°2. At the moment, the query is not working correctly, basically, some of the content and titles have apostrophes in them, and when i update that particular page it won't update. I tried using Regex.Escape but it creates a mess adding tons of \.

Answer 1

Well your update query lacks a comma between fields, but that's only the tip of a big iceberg

UPDATE pages SET title=" + pa.title + ", content =" + pa.content + " WHERE id=" + pa.id

your query written in this way is exposed to a big security problem. It is called Sql Injection

I will show a pseudocode because I don't have a sample of your actual code

string queryText = "UPDATE pages SET title=@title, content=@content WHERE id=@id"

using(SqlConnection cn = new SqlConnection(connection_string))
using(SqlCommand cmd = new SqlCommand(queryText, cn)
{
    cmd.Parameters.AddWithValue("@title", pa.title);
    cmd.Parameters.AddWithValue("@content", pa.content);
    cmd.Parameters.AddWithValue("@id", pa.id);
    cmd.ExecuteNonQuery();
}

Working in this way you avoid problems with Sql Injection, parsing of single quotes inside your values and leaking system resource because of connection not disposed.

See
Parametrized Queries
Using Statement

Answer 2

@"UPDATE pages 
  SET title= '" + pa.title + @"', 
      content = '" + pa.content + @"' 
  WHERE id= " + pa.id