0

Please Advice a little bit about INSERT or UPDATE in SQLITE

I'm using this code
NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO downloadedBook(bookID, courseID, bookName, bookPath, bookVersion, pageAmount, price, episode) VALUES (\"%@\",\"%@\", \"%@\", \"%@\", %f, %d,%f, \"%@\")",bookID,courseID,bookName,bookPath,bookVersion,pageAmount,price,episode];

for insert,

and this code

NSString *updateSQL = [NSString stringWithFormat: @"UPDATE downloadedBook SET bookID = \"%@\",courseID = \"%@\", bookName = \"%@\", bookPath = \"%@\", bookVersion = %f, pageAmount = %d, price = %f,episode = \"%@\" WHERE bookID = \"%@\"" , bookID,courseID,bookName,bookPath,bookVersion, pageAmount, price, episode,bookID];

for update.

It works fine.Now, how can I use if conditions while performing these operations? For example I would like to check "if I have this, data will update" Please Advice.

How can I do this Please Guide me.

Prince Agrawal
  • 3,619
  • 3
  • 26
  • 41
Masataga.Takashi
  • 87
  • 8
  • 2
    The first advice would be to use "prepared statements" to protect against SQL injection: http://xkcd.com/327/ – Martin R Jan 14 '14 at 08:07

1 Answers1

2

  1. Make sure the table has some unique constraint, such as bookID INTEGER PRIMARY KEY.

  2. Use INSERT OR REPLACE INTO ... with the insert syntax you have. If the insert would result in a constraint violation (e.g. bookID already exists), the conflicting rows are first removed and the data is then inserted.

Also consider using prepared statements and ? placeholders for the literals to prevent SQL syntax problems and injection attacks, and to improve performance.

laalto
  • 150,114
  • 66
  • 286
  • 303