SQL prepare statement - help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL prepare statement - help

papa
I think the problem with this function is my lack of good understanding
of the SQL.
What I intend to do is to write one int64_t to the database, but I don't
know how to write the proper sql statement, making sqlite3_prepare_v2
return a non SQLITE_OK value.
Any help is much appreciated.

void write(const std::string& table_name, const int pos, const int64_t
data) {
     ...
     std::string apstr = "INSERT INTO (";
     apstr += table_name.data();
     apstr += ", ";
     apstr += data;
     apstr += ");";
     rc = sqlite3_prepare_v2(db, apstr.data(), -1,
&binary_sql_statement, NULL);
     ...
}

Thanks in advance.

--
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free services to charitable organizations.
ArbolOne on Java Development in progress [ í ]

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL prepare statement - help

R Smith


On 2017/09/14 3:20 AM, Papa wrote:

> I think the problem with this function is my lack of good
> understanding of the SQL.
> What I intend to do is to write one int64_t to the database, but I
> don't know how to write the proper sql statement, making
> sqlite3_prepare_v2 return a non SQLITE_OK value.
> Any help is much appreciated.
>
> void write(const std::string& table_name, const int pos, const int64_t
> data) {
>     ...
>     std::string apstr = "INSERT INTO (";
>     apstr += table_name.data();
>     apstr += ", ";
>     apstr += data;
>     apstr += ");";
>     rc = sqlite3_prepare_v2(db, apstr.data(), -1,
> &binary_sql_statement, NULL);
>     ...
> }


The problem is your string builds an invalid SQL statement. You should
dump the complete string after it is built, inspecting that will teach
you very quick where the problem is.

 From what I can see, your code will build a string that looks something
like this:

INSERT INTO (table_name, 1234);

You will want to remake it so that the SQL that it builds look something
like this (I don't know your schema so I am guessing some identifiers...
but you should see the answer):

INSERT INTO table_name (name_of_int64_column) VALUES (1234);


Hope that helps, but if you don't find the solution from this, please
post the DB schema and teh string that gets built with your code.
Cheers,
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL prepare statement - help

Igor Tandetnik-2
In reply to this post by papa
On 9/13/2017 9:20 PM, Papa wrote:
> void write(const std::string& table_name, const int pos, const int64_t data) {
>      ...
>      std::string apstr = "INSERT INTO (";
>      apstr += table_name.data();
>      apstr += ", ";
>      apstr += data;

I'm pretty sure this calls string::operator+=(char), appending to the string a character whose ASCII code happens to be char(data). Which is unlikely to be what you had in mind. I suspect you get a warning from the compiler on this line, about losing significant bits.

Anyway, as already noted, the INSERT statement you are trying to build is not a valid SQL syntax.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL prepare statement - help

Jens Alfke-2


> On Sep 14, 2017, at 8:19 AM, Igor Tandetnik <[hidden email]> wrote:
>
> I'm pretty sure this calls string::operator+=(char), appending to the string a character whose ASCII code happens to be char(data). Which is unlikely to be what you had in mind. I suspect you get a warning from the compiler on this line, about losing significant bits.

Somewhat off-topic, but stringstream is a much nicer way to build up a string in C++. Use the "<<" operator to append things to it, i.e. strings, characters, numbers, then call str() to convert it to a string.

        #include <sstream>

        std::stringstream s;
        s << "INSERT INTO " << table_name << " (" << data;
        // etc.
        s << ")";
        std::string str = s.str();

Also, as for invalid SQL: Use the `sqlite3` command-line tool to interactively experiment with SQL statements until you're familiar with them. Once you have a statement that does what you want. copy it out of the terminal and put it in your code.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL prepare statement - help

Warren Young
On Sep 14, 2017, at 10:27 AM, Jens Alfke <[hidden email]> wrote:
>
> s << "INSERT INTO " << table_name << " (" << data;

You probably just wrote a SQL injection vulnerability.

Use prepared statements, [named] parameters, and the “bind” functions to build the query string instead.

    https://sqlite.org/c3ref/stmt.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQL prepare statement - help

Jens Alfke-2


> On Sep 14, 2017, at 11:10 AM, Warren Young <[hidden email]> wrote:
>
> You probably just wrote a SQL injection vulnerability.
> Use prepared statements, [named] parameters, and the “bind” functions to build the query string instead.

Yeah, you're right. I was trying to keep the example as simple as possible since the OP is a newbie, but it's not much harder to add a "?" parameter. However, some string concatenation is still needed in this case since the table name is not known at compile time.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users