Re: SQLITE PHP syntax issues - INSERT INTO db

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

Re: SQLITE PHP syntax issues - INSERT INTO db

Clemens Ladisch
Edmondo Borasio wrote:

> //If I input the $NewID manually as a string (for example '6', *it works
> fine* and updates the db correctly)
> *$query1="INSERT INTO
> Table"."(ID,name,surname)"."VALUES('6','newName','newSurname');"; *
>
> //However if I try to use the $NewID variable from above *it does't
> work....*
>
> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> '\','newName','newSurname');"; *

What is the value of $query1?


Regards,
Clemens
_______________________________________________
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: SQLITE PHP syntax issues - INSERT INTO db

Jens Alfke-2

> On Aug 5, 2017, at 6:48 AM, Edmondo Borasio <[hidden email]> wrote:
>
> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> '\','newName','newSurname');"; *

It’s a very, very bad idea to insert variable strings directly into a SQL query like this. If the content of those strings is unknown or untrusted data (as it usually is), it leaves you wide open to SQL Injection Attacks, which give an attacker full access to your database. This is probably the single most common form of attack against web applications.

Your PHP SQLite API includes facilities for safely plugging variables into the query, similar to printf. You put a placeholder like “?” into the SQL string and then pass the actual value as a separate parameter to the PHP function. That’s the right way to do it. (As a bonus, it lets you precompile the query and reuse it, which speeds up 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: SQLITE PHP syntax issues - INSERT INTO db

Igor Korot
Hi,

On Tue, Aug 8, 2017 at 12:58 PM, Jens Alfke <[hidden email]> wrote:
>
>> On Aug 5, 2017, at 6:48 AM, Edmondo Borasio <[hidden email]> wrote:
>>
>> *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
>> '\','newName','newSurname');"; *
>
> It’s a very, very bad idea to insert variable strings directly into a SQL query like this. If the content of those strings is unknown or untrusted data (as it usually is), it leaves you wide open to SQL Injection Attacks, which give an attacker full access to your database. This is probably the single most common form of attack against web applications.

Yup.
Just google "Jonny Drop All Tables". ;-)

Thank you.

>
> Your PHP SQLite API includes facilities for safely plugging variables into the query, similar to printf. You put a placeholder like “?” into the SQL string and then pass the actual value as a separate parameter to the PHP function. That’s the right way to do it. (As a bonus, it lets you precompile the query and reuse it, which speeds up your code.)
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: SQLITE PHP syntax issues - INSERT INTO db

Jens Alfke-2

> On Aug 8, 2017, at 10:03 AM, Igor Korot <[hidden email]> wrote:
>
> Just google "Jonny Drop All Tables". ;-)

“Little Bobby Tables” to be precise; here’s a direct link:
        https://xkcd.com/327/ <https://xkcd.com/327/>

—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: SQLITE PHP syntax issues - INSERT INTO db

Warren Young
On Aug 8, 2017, at 11:06 AM, Jens Alfke <[hidden email]> wrote:
>
> On Aug 8, 2017, at 10:03 AM, Igor Korot <[hidden email]> wrote:
>>
>> Just google "Jonny Drop All Tables". ;-)
>
> “Little Bobby Tables” to be precise; here’s a direct link:

Little Bobby has a web site now: http://bobby-tables.com/

‘E’s all growed up!  :)

_______________________________________________
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: SQLITE PHP syntax issues - INSERT INTO db

Stephen Chrzanowski
In reply to this post by Jens Alfke-2
To add on to what Jens mentions, with PHP, you can at least sanitize the
$NewID by using (integer)$NewID.  Any string or non integer that is
assigned to $NewID will get converted to the integer value zero.  From my
experience, typically IDs aren't stored as zero, but I've not looked at
EVERY database out there, so there could still be consequences.  Just know
your data.

With strings, you can use mysql_real_escape (Or something of the sort --
Been a few months since I've dealt with PHP/MySQL directly) which will
convert any string into a SQL safe string.  There are additional things you
can do to sanitize what you're inputting, but it'd depend on what codepage
you're putting the data in as, and where you're getting the data from.  I
only have ever dealt with ASCII, sanitize as ASCII, and strip out any other
character that doesn't fit between #01 and #FF.  Unicode is not in my
vocabulary, since I don't write in any other spoken language that doesn't
fit within 8-bits.

There is also binding you can do, as Jens mentions.  I've infrequently done
this as I've written routines that do the sanitizing for me, so I don't
need to think about it, and its a PDO thing.

On Tue, Aug 8, 2017 at 12:58 PM, Jens Alfke <[hidden email]> wrote:

>
> > On Aug 5, 2017, at 6:48 AM, Edmondo Borasio <[hidden email]>
> wrote:
> >
> > *$query1="INSERT INTO Table"."(ID,name,surname)"."VALUES(\' ' . $NewID .
> > '\','newName','newSurname');"; *
>
> It’s a very, very bad idea to insert variable strings directly into a SQL
> query like this. If the content of those strings is unknown or untrusted
> data (as it usually is), it leaves you wide open to SQL Injection Attacks,
> which give an attacker full access to your database. This is probably the
> single most common form of attack against web applications.
>
> Your PHP SQLite API includes facilities for safely plugging variables into
> the query, similar to printf. You put a placeholder like “?” into the SQL
> string and then pass the actual value as a separate parameter to the PHP
> function. That’s the right way to do it. (As a bonus, it lets you
> precompile the query and reuse it, which speeds up your code.)
>
> —Jens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: SQLITE PHP syntax issues - INSERT INTO db

Stephen Chrzanowski
In reply to this post by Warren Young
Awesome find Warren.  Thanks.

On Tue, Aug 8, 2017 at 4:08 PM, Warren Young <[hidden email]> wrote:

> On Aug 8, 2017, at 11:06 AM, Jens Alfke <[hidden email]> wrote:
> >
> > On Aug 8, 2017, at 10:03 AM, Igor Korot <[hidden email]> wrote:
> >>
> >> Just google "Jonny Drop All Tables". ;-)
> >
> > “Little Bobby Tables” to be precise; here’s a direct link:
>
> Little Bobby has a web site now: http://bobby-tables.com/
>
> ‘E’s all growed up!  :)
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users