Like Tree2Likes
  • 1 Post By Salem
  • 1 Post By laserlight

SQLite, is it possible to put a string into an argument?

This is a discussion on SQLite, is it possible to put a string into an argument? within the C Programming forums, part of the General Programming Boards category; Hi guys, I'm half competent with C, I've spent all morning looking over the SQLite documentation and I can't work ...

  1. #1
    Registered User
    Join Date
    Mar 2012
    Posts
    5

    SQLite, is it possible to put a string into an argument?

    Hi guys,

    I'm half competent with C, I've spent all morning looking over the SQLite documentation and I can't work out what I need to do.

    I need a program which will ask user for their input, and then in-turn puts that into an SQL statement.

    For example, the program would prompt the user to enter their name, after they've done that it stores their name in a variable and puts that variable into the following argument

    INSERT INTO tablename VALUES(value1,value2,mystring)

    Not asking for anyone to do it but could someone point me in the right direction, or at least confirm this is possible? Smashing my head against the wall at the moment.

    Thanks

  2. #2
    and the hat of int overfl Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,822
    Sure, start with
    const char *template = "INSERT INTO %s VALUES(value1,value2,%s)";

    Then do
    sprintf(buff,template,myTable,myKey);

    But you need to make sure you have extremely good input string sanitisation (and validation).
    Sending malformed SQL queries into a database is a well documented DoS attack - read up on SQL injection attacks before proceeding.
    stahta01 likes this.
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  3. #3
    Registered User
    Join Date
    Mar 2012
    Posts
    5
    Thanks for your reply Salem, just wondering what the relevance is of using sprintf?

    Currently I'm using this code to insert my data into the table:

    Code:
    queries[ind++] = "INSERT INTO table VALUES('string','string',0)";
        retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
    Which works fine, but I need scanf or similar to store the users info in [b[*template[/b] like you suggested. All I'm getting is segmentation faults at the moment.

    Thanks

  4. #4
    and the hat of int overfl Salem's Avatar
    Join Date
    Aug 2001
    Location
    The edge of the known universe
    Posts
    32,822
    Post something that segfaults.

    But to be honest, if you can't find a relatively simple segfault, how are you going to defend SQL injection attacks?

    Using scanf() to read the user input is NOT the way to go.

    Use fgets() to read a line of input.
    Parse the line to look for a table name.
    Make sure it matches EXACTLY one of the known table names. Don't blindly assume something vague like "string of isascii() chars" is a strong enough test.

    Users complaining that they can't access table "foo" (if foo is a valid table name) is a lot easier to fix than "WTF happened to my database!?"
    If you dance barefoot on the broken glass of undefined behaviour, you've got to expect the occasional cut.
    If at first you don't succeed, try writing your phone number on the exam paper.
    I support http://www.ukip.org/ as the first necessary step to a free Europe.

  5. #5
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    22,265
    I am curious to know: why do you need the table name to be variable? After all, you only mentioned getting the user's name as input, so where does the table name come from? If you are going to use the user's name as the table name, then you're probably doing something wrong, e.g., you need to go back and think about normalisation.

    If you really do need the table name to be variable, then you pretty much have to use sprintf after checking that the input is valid, but take care to quote the table name (with double quotes). The value to be inserted should be bound to a parameter within a prepared statement, not handled with sprintf.
    C + C++ Compiler: MinGW port of GCC
    Version Control System: Bazaar

    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  6. #6
    Registered User
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by Salem View Post
    But to be honest, if you can't find a relatively simple segfault, how are you going to defend SQL injection attacks?
    This is for a university project and the requirements state we only need to have a program with basic interaction with a database, we wont be expected to guard against injection attacks. Saying that it will still have good string sanitation and validation.

    Quote Originally Posted by laserlight View Post
    I am curious to know: why do you need the table name to be variable? After all, you only mentioned getting the user's name as input, so where does the table name come from? If you are going to use the user's name as the table name, then you're probably doing something wrong, e.g., you need to go back and think about normalisation.

    If you really do need the table name to be variable, then you pretty much have to use sprintf after checking that the input is valid, but take care to quote the table name (with double quotes). The value to be inserted should be bound to a parameter within a prepared statement, not handled with sprintf.
    Sorry I should not have put the table name in bold, this isn't for the user to change. The application is a basic address book, the only inputs they will be giving are for fields like FIRST_NAME etc.

    This is what I'm trying to do more or less.
    Code:
    cont char *userstring = "test";
    
    queries[ind++] = ("INSERT INTO table VALUES('%s','string',0)", userstring);
    
    retval = sqlite3_exec(handle,queries[ind-1],0,0,0);

    Error I get is:
    warning: assignment from incompatible pointer type

    Thank you for your replies so far I appreciate it. Sorry if I haven't been clear I'm have trouble with C as I'm not really used to
    imperative programming.
    Last edited by Kashinoda; 03-16-2012 at 01:39 PM.

  7. #7
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    22,265
    Quote Originally Posted by Kashinoda
    Sorry I should not have put the table name in bold, this isn't for the user to change. The application is a basic address book, the only inputs they will be giving are for fields like FIRST_NAME etc.
    Ah, that makes sense.

    Quote Originally Posted by Kashinoda
    Error I get is: warning: assignment from incompatible pointer type
    Unfortunately, because you did not show your declaration of queries, I cannot tell you exactly why this is so. However, I note that the expression ("INSERT INTO table VALUES('%s','string',0)", userstring) expression uses the comma operator, thus the left subexpression is evaluated (it is just a string, so there's no side effect), then the right subexpression is evaluated and becomes the value of the comma expression, thus that line has a net effect:
    Code:
    queries[ind++] = userstring;
    Why do you need an array of queries anyway?

    Quote Originally Posted by Kashinoda
    This is what I'm trying to do more or less.
    Stop using sqlite3_exec except for SQL statements for which everything is hard coded. The appropriate way to do this is to use sqlite3_prepare_v2 to prepare your statement with a parameter, then use sqlite3_bind_text to bind your string to the parameter, and finally use sqlite3_step to execute the prepared statement.
    stahta01 likes this.
    C + C++ Compiler: MinGW port of GCC
    Version Control System: Bazaar

    Look up a C++ Reference and learn How To Ask Questions The Smart Way

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. MinGW and SQLite
    By CarlGB in forum C Programming
    Replies: 5
    Last Post: 02-15-2010, 10:30 PM
  2. SQLite questions
    By ac251404 in forum C++ Programming
    Replies: 6
    Last Post: 08-22-2006, 12:00 PM
  3. How to using SQLite with WINAPI
    By nostromos in forum Windows Programming
    Replies: 1
    Last Post: 10-02-2005, 02:53 AM
  4. how to pass a string as an argument?
    By waxydock in forum C++ Programming
    Replies: 3
    Last Post: 03-26-2005, 05:40 PM
  5. Using SQLite?
    By IM! in forum C++ Programming
    Replies: 18
    Last Post: 03-16-2005, 04:00 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21