[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

I would like thanks to all contributors of this interesting debate, for their 
effort.

L. 


> As with Rob, this is my final say as well.

> On Fri, Sep 11, 2015 at 1:38 PM, Petr L?z?ovsk? wrote:

>> 1. Security through obscurity is your first mistake. There is no such thing.
>  
>  Interesting It does not exist, but it have article on wikipedia. Sounds 
> like UFO or Yetti...




> "Security through Obscurity" in layman terms is that you provide no 
> additional valid information to whomever is accessing your system.  If you're 
> URL provides CGI in its path, it points (Doesn't necessarily MEAN) more at 
> that it is a Linux box than a Windows box.  That doesn't mean it isn't, just 
> that it is more likely to.  Looking at HTTP headers, you can find out what OS 
> your script is running on, what version of PHP, ASP, .NET, whatever.  Black 
> Hats typically keep a very long list of found security holes on every single 
> version of whatever script processor you're using.  Obscurity means you're 
> just muddying the waters, or hiding facts, or acting like an outright liar.  
> It doesn't mean your system becomes locked down and people aren't going to be 
> interested ESPECIALLY if you're found to be on a fat pipe to the net.  
> Because Security through Obscurity is becoming more mainstream, I'm sure 
> these Black Hats don't even look at that and have techniques to just get what 
> they need done.

>  
>  
 >> 2. Assuming that nobody is writing CGI scripts on Windows Servers is your 
 >> next mistake. A lot of systems still do this, a lot of old systems still 
 >> use this technique and some new ones, The attack vector is not necessarily 
 >> through your CGI script itself but through the Windows Web server. Unless 
 >> you have patched and patched and patched your web server, you will be 
 >> attacked.
>  
>  Of course I keep my web server software up-to-date, why do you think I do 
> not did it? I am talking here about my scripts, not about the server SW. But 
> the server SW is relatively rare too...




> "Scripts" and the underlying script processors are what do the processing.  
> OS updates are one thing, but the script processing engine is another, and so 
> are the scripts you write.  If any one of those three links in the chain are 
> broken, then buh-bye security.  The script processor talks to the OS to get 
> things done, such as allocate memory, set aside a portion of drive space, 
> execute other code, etc.  If you don't sanitize your inputs 100% of the time 
> properly, you're subject to losing a LOT of information or having your 
> machine taken over, or as Rob mentions, resources sold (A thought I hadn't 
> even  thought about).  The 'execute other code' should scare the bejezus out 
> of you.  If your system is allowed to run other code (Say, for example, [ ls 
> -ltr /etc ] to get a directory list), if you don't sanitize your code, 
> someone can (Not might, CAN if you don't sanitize) change that to [ ls -ltr 
> /etc ; rm -rf /etc ] and if Apache has access to write anything there, you 
> have a new install of an OS to do.


> The company I work for sells software for the web that runs off both IIS and 
> Apache under Linux.  Both are scrutinized for security even though the main 
> portion of the software is behind a paywall (Need credentials to get into the 
> heart and main functions of the system) and all communications are via HTTPS. 
>  All HTTPS does is ensure that no one else knows what you're saying on the 
> socket, essentially.  It doesn't mean that the system isn't hackable.  If we 
> didn't ensure that the login field on the login page wasn't sanitized, then 
> we're pretty much bending over.

>  
>  
 >> 3. You assume that nobody is interested in your machine. Wrong. A lot of 
 >> people are very interested as they can add your hacked server to their 
 >> bonnet and sell your resources on. Your machine does not have to be 
 >> publicised at all. As an example, I have a private server which I use. It 
 >> has no DNS entry (a common way to search for machines), so is only 
 >> accessible through an IP address which has never been published. It only 
 >> has a single ssh port open and port 80 for a private web server running 
 >> some software there rest of the machine is locked down as best I can. The 
 >> lock down took me a day to do. It is not trivial. My last weekly report 
 >> showed over 200,000 attempts to break into the machine via ssh, http, and 
 >> various CGI exploits. Thats 200,000 robot attempts, the most prevalent was 
 >> an ssh attempt from a single machine which accounted for 72,000 goes. A 
 >> public web server I have has over 1M hacking attempts per week. This is for 
 >> a low usage machine.
>  
>  Script kiddies starting codes writen to attack widely spreaded systems, 
> otherwise it will be not much fun. Some of this codes could be specialized to 
> intrude minor systems, but I have doubts there are number of working scripts 
> to successfuly 

[sqlite] BEGINNER - Transactions in shell script

[Stephen Chrzanowski]

As with Rob, this is my final say as well.

On Fri, Sep 11, 2015 at 1:38 PM, Petr L?z?ovsk?  wrote:

> > 1. Security through obscurity is your first mistake. There is no such
> thing.
>
> Interesting It does not exist, but it have article on wikipedia.
> Sounds like UFO or Yetti...
>

"Security through Obscurity" in layman terms is that you provide no
additional valid information to whomever is accessing your system.  If
you're URL provides CGI in its path, it points (Doesn't necessarily MEAN)
more at that it is a Linux box than a Windows box.  That doesn't mean it
isn't, just that it is more likely to.  Looking at HTTP headers, you can
find out what OS your script is running on, what version of PHP, ASP, .NET,
whatever.  Black Hats typically keep a very long list of found security
holes on every single version of whatever script processor you're using.
Obscurity means you're just muddying the waters, or hiding facts, *or
acting like an outright liar*.  It doesn't mean your system becomes locked
down and people aren't going to be interested ESPECIALLY if you're found to
be on a fat pipe to the net.  Because Security through Obscurity is
becoming more mainstream, I'm sure these Black Hats don't even look at that
and have techniques to just get what they need done.


>
> > 2. Assuming that nobody is writing CGI scripts on Windows Servers is
> your next mistake. A lot of systems still do this, a lot of old systems
> still use this technique and some new ones, The attack vector is not
> necessarily through your CGI script itself but through the Windows Web
> server. Unless you have patched and patched and patched your web server,
> you will be attacked.
>
> Of course I keep my web server software up-to-date, why do you think I do
> not did it? I am talking here about my scripts, not about the server SW.
> But the server SW is relatively rare too...
>

"Scripts" and the underlying script processors are what do the processing.
OS updates are one thing, but the script processing engine is another, and
so are the scripts you write.  If any one of those three links in the chain
are broken, then buh-bye security.  The script processor talks to the OS to
get things done, such as allocate memory, set aside a portion of drive
space, execute other code, etc.  If you don't sanitize your inputs 100% of
the time properly, you're subject to losing a LOT of information or having
your machine taken over, or as Rob mentions, resources sold (A thought I
hadn't even  thought about).  The 'execute other code' should scare the
bejezus out of you.  If your system is allowed to run other code (Say, for
example, [ ls -ltr /etc ] to get a directory list), if you don't sanitize
your code, someone can (Not might, CAN if you don't sanitize) change that
to [ ls -ltr /etc ; rm -rf /etc ] and if Apache has access to write
anything there, you have a new install of an OS to do.

The company I work for sells software for the web that runs off both IIS
and Apache under Linux.  Both are scrutinized for security even though the
main portion of the software is behind a paywall (Need credentials to get
into the heart and main functions of the system) and all communications are
via HTTPS.  All HTTPS does is ensure that no one else knows what you're
saying on the socket, essentially.  It doesn't mean that the system isn't
hackable.  If we didn't ensure that the login field on the login page
wasn't sanitized, then we're pretty much bending over.


>
> > 3. You assume that nobody is interested in your machine. Wrong. A lot of
> people are very interested as they can add your hacked server to their
> bonnet and sell your resources on. Your machine does not have to be
> publicised at all. As an example, I have a private server which I use. It
> has no DNS entry (a common way to search for machines), so is only
> accessible through an IP address which has never been published. It only
> has a single ssh port open and port 80 for a private web server running
> some software there rest of the machine is locked down as best I can. The
> lock down took me a day to do. It is not trivial. My last weekly report
> showed over 200,000 attempts to break into the machine via ssh, http, and
> various CGI exploits. Thats 200,000 robot attempts, the most prevalent was
> an ssh attempt from a single machine which accounted for 72,000 goes. A
> public web server I have has over 1M hacking attempts per week. This is for
> a low usage machine.
>
> Script kiddies starting codes writen to attack widely spreaded systems,
> otherwise it will be not much fun. Some of this codes could be specialized
> to intrude minor systems, but I have doubts there are number of working
> scripts to successfuly intrude systems with rare occurance.
>
> Real hackers, those who are experienced in writing WORKING code targeted
> to intrude one specific rare system, need a REAL reason to did such job. My
> system does not offer such reason
>
>
"Real hackers" is funny.  I've never met a 

[sqlite] BEGINNER - Transactions in shell script

[Rob Willett]

Petr,

Since this is the SQLite mailing list, we are moving away from the intentions 
of the list, however I think your points need addressing as they may be 
relevant to other people using this mailing list. I apologise to other people 
if this is off topic but I think its important enough to answer.

> On 11 Sep 2015, at 18:38, Petr L?z?ovsk?  wrote:
> 
>> 1. Security through obscurity is your first mistake. There is no such thing. 
> 
> Interesting It does not exist, but it have article on wikipedia. Sounds 
> like UFO or Yetti?   

Security through obscurity means that you assume that because nobody knows your 
system or knows your code then you are secure. That is what I was referring to. 
The article I assume you refer to

https://en.wikipedia.org/wiki/Security_through_obscurity 


makes this very point.

> 
>> 2. Assuming that nobody is writing CGI scripts on Windows Servers is your 
>> next mistake. A lot of systems still do this, a lot of old systems still use 
>> this technique and some new ones, The attack vector is not necessarily 
>> through your CGI script itself but through the Windows Web server. Unless 
>> you have patched and patched and patched your web server, you will be 
>> attacked. 
> 
> Of course I keep my web server software up-to-date, why do you think I do not 
> did it? I am talking here about my scripts, not about the server SW. But the 
> server SW is relatively rare too... 

I do not know what you do with your server, I would like to think you keep it 
patched up, but since you did not say all I can do is assume the worst and be 
proven wrong. However have you locked down every port, have you removed 
everything that is not needed, have to configured your applications that are 
front facing to be as secure as possible, have you put https for the web 
traffic that needs to be transmitted, have you checked that another machine on 
your local internet cannot get access to your machine, a side door approach. 
The scripts themselves may be secure, but the mechanism needed to run them 
needs to be just as secure. 

> 
>> 3. You assume that nobody is interested in your machine. Wrong. A lot of 
>> people are very interested as they can add your hacked server to their 
>> bonnet and sell your resources on. Your machine does not have to be 
>> publicised at all. As an example, I have a private server which I use. It 
>> has no DNS entry (a common way to search for machines), so is only 
>> accessible through an IP address which has never been published. It only has 
>> a single ssh port open and port 80 for a private web server running some 
>> software there rest of the machine is locked down as best I can. The lock 
>> down took me a day to do. It is not trivial. My last weekly report showed 
>> over 200,000 attempts to break into the machine via ssh, http, and various 
>> CGI exploits. Thats 200,000 robot attempts, the most prevalent was an ssh 
>> attempt from a single machine which accounted for 72,000 goes. A public web 
>> server I have has over 1M hacking attempts per week. This is for a low usage 
>> machine. 
> 
> Script kiddies starting codes writen to attack widely spreaded systems, 
> otherwise it will be not much fun. Some of this codes could be specialized to 
> intrude minor systems, but I have doubts there are number of working scripts 
> to successfuly intrude systems with rare occurance.
> 
> Real hackers, those who are experienced in writing WORKING code targeted to 
> intrude one specific rare system, need a REAL reason to did such job. My 
> system does not offer such reason?.

If you are using a Windows OS then your system is widely available. There may 
be millions of machines running your version of the OS, so you are a target 
from script kiddies. This comes back to Security through Obscurity. Your system 
is not unique and the resources it offers of an internet connection and 
processing power makes it attractive. The people searching the internet do not 
know the details of your machine, they are looking for machines to add to 
botnets. The fact you run a local database on it is of no interest, they want 
the machines to use to rent out for a DDOS attack or password cracking or spam 
sending. 

> 
>> I give your machine less than 24 hours once it is live on the internet if 
>> you put it on without taking security seriously. You need to get the OS 
>> patched up, the ports closed down, the web server patched up and correctly 
>> configured. Out of the box the security on a Windows server (depending on 
>> the version) is poor. You need to learn what you need to do (and there are 
>> loads of guides on the internet) otherwise your server will be owned by 
>> somebody else very quickly. 
> 
> As I already wrote, not using IIS. OS is protected by manualy configured 
> firewall. By concept Security through obscurity using this one 
> http://wipfw.sourceforge.net/ Intruding script perform OS 

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

> 1. Security through obscurity is your first mistake. There is no such thing. 

Interesting It does not exist, but it have article on wikipedia. Sounds 
like UFO or Yetti...   

> 2. Assuming that nobody is writing CGI scripts on Windows Servers is your 
> next mistake. A lot of systems still do this, a lot of old systems still use 
> this technique and some new ones, The attack vector is not necessarily 
> through your CGI script itself but through the Windows Web server. Unless you 
> have patched and patched and patched your web server, you will be attacked. 

Of course I keep my web server software up-to-date, why do you think I do not 
did it? I am talking here about my scripts, not about the server SW. But the 
server SW is relatively rare too... 

> 3. You assume that nobody is interested in your machine. Wrong. A lot of 
> people are very interested as they can add your hacked server to their bonnet 
> and sell your resources on. Your machine does not have to be publicised at 
> all. As an example, I have a private server which I use. It has no DNS entry 
> (a common way to search for machines), so is only accessible through an IP 
> address which has never been published. It only has a single ssh port open 
> and port 80 for a private web server running some software there rest of the 
> machine is locked down as best I can. The lock down took me a day to do. It 
> is not trivial. My last weekly report showed over 200,000 attempts to break 
> into the machine via ssh, http, and various CGI exploits. Thats 200,000 robot 
> attempts, the most prevalent was an ssh attempt from a single machine which 
> accounted for 72,000 goes. A public web server I have has over 1M hacking 
> attempts per week. This is for a low usage machine. 

Script kiddies starting codes writen to attack widely spreaded systems, 
otherwise it will be not much fun. Some of this codes could be specialized to 
intrude minor systems, but I have doubts there are number of working scripts to 
successfuly intrude systems with rare occurance.

Real hackers, those who are experienced in writing WORKING code targeted to 
intrude one specific rare system, need a REAL reason to did such job. My system 
does not offer such reason

> I give your machine less than 24 hours once it is live on the internet if you 
> put it on without taking security seriously. You need to get the OS patched 
> up, the ports closed down, the web server patched up and correctly 
> configured. Out of the box the security on a Windows server (depending on the 
> version) is poor. You need to learn what you need to do (and there are loads 
> of guides on the internet) otherwise your server will be owned by somebody 
> else very quickly. 

As I already wrote, not using IIS. OS is protected by manualy configured 
firewall. By concept Security through obscurity using this one 
http://wipfw.sourceforge.net/ Intruding script perform OS detection first, but 
do  not expect BSD firewall on Windows... Simple as it. Did you understand STO 
concept now?

L.

BTW: There is smile next this "trust me" this obviously means: Do not took it 
too seriously

> To be blunt you have misunderstood computer security, Saying ?trust me? 
> doesn?t work. 

> Best of luck,

> Rob

>> On 11 Sep 2015, at 13:42, Petr L?z?ovsk?  wrote:

>> There is a major difference: You are talking about SSH and Linux, this 
>> combination running on hundred milions of network devices accross whole 
>> internet. Thus develop intruding scripts does make sense. But I am using 
>> Windows shell scripts as CGI, which is EXTREMELY rare. Who will study this 
>> technique to intrude my (or very few another) systems? No one trust me 
>> ;-)

>> L.

>> BTW: If someone did it anyway, I will give him medal and start experinces 
>> sharing to him 

>>> You'd be surprised by what is out there trying to get into your system.

>>> I had port 22 open on my home router to go to a Linux machine so I could 
>>> SSH into my home network from anywhere in the world, even though I rarely 
>>> ever leave the 519 area code.  One day I went to look at my messages log 
>>> file and noted numerous brute force attempts to get into my machine.  
>>> Fortunately, the machine is setup so that you can't SSH in as root, and the 
>>> single login name that has any kind of access root capable access is 
>>> intentionally camel cased to thwart name dictionary attacks.  The attacks 
>>> were automated at their end, obviously, but if you have a machine exposed, 
>>> someone is going to have software that will do anything and everything to 
>>> gain access through whatever weakest link you have.


>>> I'm on a residential cable line, with an IP that changes periodically, 
>>> however, I'm still subject to attacks.  SSH is a common thing, and what you 
>>> have written may not be interesting to the hacker space as a whole, 
>>> however, there is that one idiot out there that WILL take the time to break 
>>> into your system for 

[sqlite] BEGINNER - Transactions in shell script

[Rob Willett]

Petr,

You are making a number of fundamental mistakes with your security.  

1. Security through obscurity is your first mistake. There is no such thing. 

2. Assuming that nobody is writing CGI scripts on Windows Servers is your next 
mistake. A lot of systems still do this, a lot of old systems still use this 
technique and some new ones, The attack vector is not necessarily through your 
CGI script itself but through the Windows Web server. Unless you have patched 
and patched and patched your web server, you will be attacked. 

3. You assume that nobody is interested in your machine. Wrong. A lot of people 
are very interested as they can add your hacked server to their bonnet and sell 
your resources on. Your machine does not have to be publicised at all. As an 
example, I have a private server which I use. It has no DNS entry (a common way 
to search for machines), so is only accessible through an IP address which has 
never been published. It only has a single ssh port open and port 80 for a 
private web server running some software there rest of the machine is locked 
down as best I can. The lock down took me a day to do. It is not trivial. My 
last weekly report showed over 200,000 attempts to break into the machine via 
ssh, http, and various CGI exploits. Thats 200,000 robot attempts, the most 
prevalent was an ssh attempt from a single machine which accounted for 72,000 
goes. A public web server I have has over 1M hacking attempts per week. This is 
for a low usage machine. 

I give your machine less than 24 hours once it is live on the internet if you 
put it on without taking security seriously. You need to get the OS patched up, 
the ports closed down, the web server patched up and correctly configured. Out 
of the box the security on a Windows server (depending on the version) is poor. 
You need to learn what you need to do (and there are loads of guides on the 
internet) otherwise your server will be owned by somebody else very quickly. 

To be blunt you have misunderstood computer security, Saying ?trust me? doesn?t 
work. 

Best of luck,

Rob

> On 11 Sep 2015, at 13:42, Petr L?z?ovsk?  wrote:
> 
> There is a major difference: You are talking about SSH and Linux, this 
> combination running on hundred milions of network devices accross whole 
> internet. Thus develop intruding scripts does make sense. But I am using 
> Windows shell scripts as CGI, which is EXTREMELY rare. Who will study this 
> technique to intrude my (or very few another) systems? No one trust me ;-)
> 
> L.
> 
> BTW: If someone did it anyway, I will give him medal and start experinces 
> sharing to him 
> 
>> You'd be surprised by what is out there trying to get into your system.
> 
>> I had port 22 open on my home router to go to a Linux machine so I could SSH 
>> into my home network from anywhere in the world, even though I rarely ever 
>> leave the 519 area code.  One day I went to look at my messages log file and 
>> noted numerous brute force attempts to get into my machine.  Fortunately, 
>> the machine is setup so that you can't SSH in as root, and the single login 
>> name that has any kind of access root capable access is intentionally camel 
>> cased to thwart name dictionary attacks.  The attacks were automated at 
>> their end, obviously, but if you have a machine exposed, someone is going to 
>> have software that will do anything and everything to gain access through 
>> whatever weakest link you have.
> 
> 
>> I'm on a residential cable line, with an IP that changes periodically, 
>> however, I'm still subject to attacks.  SSH is a common thing, and what you 
>> have written may not be interesting to the hacker space as a whole, however, 
>> there is that one idiot out there that WILL take the time to break into your 
>> system for jollies.
> 
> 
> 
> 
> 
>> On Fri, Sep 11, 2015 at 6:12 AM, Petr L?z?ovsk?  wrote:
> 
> 
>> Never heard about this. Thinked about this a bit, but have no idea how it 
>> could menace my CGI application. But as far I am a beginner, expecting it 
>> could be a menace but rely on Security by obscurity. Some time a go, when I 
>> start writing CGI powered by windows shell scripts, I have serched (almost 
>> whole) internet for some examples or informations, but I found nothing. 
>> That means I am lonely with this technique ;-) No hacker will study such 
>> weird technique to intrude only one system on whole internet ;-)
>> 
>> L.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

There is a major difference: You are talking about SSH and Linux, this 
combination running on hundred milions of network devices accross whole 
internet. Thus develop intruding scripts does make sense. But I am using 
Windows shell scripts as CGI, which is EXTREMELY rare. Who will study this 
technique to intrude my (or very few another) systems? No one trust me ;-)

L.

BTW: If someone did it anyway, I will give him medal and start experinces 
sharing to him 

> You'd be surprised by what is out there trying to get into your system.

> I had port 22 open on my home router to go to a Linux machine so I could SSH 
> into my home network from anywhere in the world, even though I rarely ever 
> leave the 519 area code.  One day I went to look at my messages log file and 
> noted numerous brute force attempts to get into my machine.  Fortunately, the 
> machine is setup so that you can't SSH in as root, and the single login name 
> that has any kind of access root capable access is intentionally camel cased 
> to thwart name dictionary attacks.  The attacks were automated at their end, 
> obviously, but if you have a machine exposed, someone is going to have 
> software that will do anything and everything to gain access through whatever 
> weakest link you have.


> I'm on a residential cable line, with an IP that changes periodically, 
> however, I'm still subject to attacks.  SSH is a common thing, and what you 
> have written may not be interesting to the hacker space as a whole, however, 
> there is that one idiot out there that WILL take the time to break into your 
> system for jollies.





> On Fri, Sep 11, 2015 at 6:12 AM, Petr L?z?ovsk?  wrote:


>  Never heard about this. Thinked about this a bit, but have no idea how it 
> could menace my CGI application. But as far I am a beginner, expecting it 
> could be a menace but rely on Security by obscurity. Some time a go, when I 
> start writing CGI powered by windows shell scripts, I have serched (almost 
> whole) internet for some examples or informations, but I found nothing. 
> That means I am lonely with this technique ;-) No hacker will study such 
> weird technique to intrude only one system on whole internet ;-)
>  
>  L.

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

> SQL commands do not need to be on multiple lines (they only need a
> semicolon after each command).
> But dot commands do.

Good to know

> Have you tried the following?
> (ECHO .bail on
> ECHO %multiple commands%) | sqlite3.exe %dbname%

This could be solution, thanks

> But, much more importantly (particularly since you included BEGINNER in big
> letters in your subject line): are you protecting your DB against SQL
> injection attacks? I imagine this would be rather hard to do from a script,
> unless you have already sanitized data coming in.

Never heard about this. Thinked about this a bit, but have no idea how it could 
menace my CGI application. But as far I am a beginner, expecting it could be a 
menace but rely on Security by obscurity. Some time a go, when I start writing 
CGI powered by windows shell scripts, I have serched (almost whole) internet 
for some examples or informations, but I found nothing. That means I am 
lonely with this technique ;-) No hacker will study such weird technique to 
intrude only one system on whole internet ;-) 

L.

> On 7 September 2015 at 18:53, Simon Slavin  wrote:


>> On 7 Sep 2015, at 8:00am, Petr L?z?ovsk?  wrote:

>> > Uhgh Spent lot of time to made whole scripts bundle to use no
>> tempfiles (avoiding I/Os), and not this ;-)
>> > OK, few more questions:
>> >
>> > Does sqlite3 expect exactly one statement per one line on input?
>> > Or put it into command line? sqlite3.exe %db% "%multiline_statement%"
>> (have seriuos doubts about this solution)
>> > Or this? echo "%multiline_statement%"|sqlite3.exe %db%

>> I can tell you if this works on the Macintosh version but this may not
>> necessarily mean it works on the version for your operating system.

>> If not, create a text file with the commands in then feed it the text
>> file.  The command could look like

>> sqlite3 %db% < theCommands.txt

>> I think the structure you used above ...

>> Unix/Linux:
>> cat theCommands.txt | sqlite3.exe %db%
>> Windows:
>> type theCommands.txt | sqlite3.exe %db%

>> will also work.

>> You may find it useful to figure out a way of generating a filename which
>> will never be repeated.  When I try to do this I usually end up with
>> something which relates to the current time.

>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BEGINNER - Transactions in shell script

[Stephen Chrzanowski]

You'd be surprised by what is out there trying to get into your system.

I had port 22 open on my home router to go to a Linux machine so I could
SSH into my home network from anywhere in the world, even though I rarely
ever leave the 519 area code.  One day I went to look at my messages log
file and noted numerous brute force attempts to get into my machine.
Fortunately, the machine is setup so that you can't SSH in as root, and the
single login name that has any kind of access root capable access is
intentionally camel cased to thwart name dictionary attacks.  The attacks
were automated at their end, obviously, but if you have a machine exposed,
someone is going to have software that will do anything and everything to
gain access through whatever weakest link you have.

I'm on a residential cable line, with an IP that changes periodically,
however, I'm still subject to attacks.  SSH is a common thing, and what you
have written may not be interesting to the hacker space as a whole,
however, there is that one idiot out there that WILL take the time to break
into your system for jollies.


On Fri, Sep 11, 2015 at 6:12 AM, Petr L?z?ovsk?  wrote:

>
> Never heard about this. Thinked about this a bit, but have no idea how it
> could menace my CGI application. But as far I am a beginner, expecting it
> could be a menace but rely on Security by obscurity. Some time a go, when I
> start writing CGI powered by windows shell scripts, I have serched (almost
> whole) internet for some examples or informations, but I found nothing.
> That means I am lonely with this technique ;-) No hacker will study such
> weird technique to intrude only one system on whole internet ;-)
>
> L.
>

[sqlite] BEGINNER - Transactions in shell script

[John McKown]

On Fri, Sep 11, 2015 at 6:51 AM, Stephen Chrzanowski 
wrote:

> You'd be surprised by what is out there trying to get into your system.
>
> I had port 22 open on my home router to go to a Linux machine so I could
> SSH into my home network from anywhere in the world, even though I rarely
> ever leave the 519 area code.  One day I went to look at my messages log
> file and noted numerous brute force attempts to get into my machine.
> Fortunately, the machine is setup so that you can't SSH in as root, and the
> single login name that has any kind of access root capable access is
> intentionally camel cased to thwart name dictionary attacks.  The attacks
> were automated at their end, obviously, but if you have a machine exposed,
> someone is going to have software that will do anything and everything to
> gain access through whatever weakest link you have.
>

?I do the same! With two modifications. I don't use port 22. I use an
"ephemeral" port (above 1024)? which most attack software doesn't even try.
This is easy to accomplish either by making SSH listen on another port, or
by having the WAN connected router redirect the port from "" on the
"outside" to port 22 on the "inside". Also, I _only_ use a digital
certificate for SSH. I don't allow use of passwords at all.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[sqlite] BEGINNER - Transactions in shell script

[Barry]

SQL commands do not need to be on multiple lines (they only need a
semicolon after each command).
But dot commands do.

Have you tried the following?
(ECHO .bail on
ECHO %multiple commands%) | sqlite3.exe %dbname%

But, much more importantly (particularly since you included BEGINNER in big
letters in your subject line): are you protecting your DB against SQL
injection attacks? I imagine this would be rather hard to do from a script,
unless you have already sanitized data coming in.


On 7 September 2015 at 18:53, Simon Slavin  wrote:

>
> On 7 Sep 2015, at 8:00am, Petr L?z?ovsk?  wrote:
>
> > Uhgh Spent lot of time to made whole scripts bundle to use no
> tempfiles (avoiding I/Os), and not this ;-)
> > OK, few more questions:
> >
> > Does sqlite3 expect exactly one statement per one line on input?
> > Or put it into command line? sqlite3.exe %db% "%multiline_statement%"
> (have seriuos doubts about this solution)
> > Or this? echo "%multiline_statement%"|sqlite3.exe %db%
>
> I can tell you if this works on the Macintosh version but this may not
> necessarily mean it works on the version for your operating system.
>
> If not, create a text file with the commands in then feed it the text
> file.  The command could look like
>
> sqlite3 %db% < theCommands.txt
>
> I think the structure you used above ...
>
> Unix/Linux:
> cat theCommands.txt | sqlite3.exe %db%
> Windows:
> type theCommands.txt | sqlite3.exe %db%
>
> will also work.
>
> You may find it useful to figure out a way of generating a filename which
> will never be repeated.  When I try to do this I usually end up with
> something which relates to the current time.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 7 Sep 2015, at 8:00am, Petr L?z?ovsk?  wrote:

> Uhgh Spent lot of time to made whole scripts bundle to use no tempfiles 
> (avoiding I/Os), and not this ;-)
> OK, few more questions:
> 
> Does sqlite3 expect exactly one statement per one line on input?
> Or put it into command line? sqlite3.exe %db% "%multiline_statement%" (have 
> seriuos doubts about this solution)
> Or this? echo "%multiline_statement%"|sqlite3.exe %db%

I can tell you if this works on the Macintosh version but this may not 
necessarily mean it works on the version for your operating system.

If not, create a text file with the commands in then feed it the text file.  
The command could look like

sqlite3 %db% < theCommands.txt

I think the structure you used above ...

Unix/Linux:
cat theCommands.txt | sqlite3.exe %db%
Windows:
type theCommands.txt | sqlite3.exe %db%

will also work.

You may find it useful to figure out a way of generating a filename which will 
never be repeated.  When I try to do this I usually end up with something which 
relates to the current time.

Simon.

[sqlite] BEGINNER - Transactions in shell script

[Rowan Worth]

On 6 September 2015 at 18:26, Luuk  wrote:
>
> Suppose i have 'test.sql':
> .echo on
> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> INSERT INTO test VALUES(3,'test3',3);
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;
>
> And a database 'test.sqlite' with the following table:
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test(i int, t text);
> COMMIT;
>
> If i do:
> type test.sql | sqlite3.exe test.sqlite
>
> Than there is an error executing this line:
> INSERT INTO test VALUES(3,'test3',3);
>
> But the transaction is not stopped, or rolled back.
> What am i missing?
>

The default behaviour of the sqlite3 shell when a statement encounters an
error is to continue executing the remaining statements. Try sqlite3.exe
-bail and instead it will stop at the first error. In that case the COMMIT
statement is never executed so the statements within the half-finished
transaction don't take effect.

The DELETE of course remains effective - it is done and dusted by the time
the transaction starts.

-Rowan

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 06-09-15 20:46, Petr L?z?ovsk? wrote:
> Thanks to all answers. Are principles of such script OK, or I miss something?
>
>
> set "_error="
> sqlite3.exe %db% BEGIN;
> for /f "tokens=2,3 delims=," %%a in (data.csv) do (
>   sqlite3 %db% "INSERT INTO payments(id,amount) VALUES 
> ('%%a','%%b')";
>   if errorlevel 1 set "_error=y"
>   )
> if not defined _error (
>   sqlite3.exe %db% COMMIT;
>   ) else (
>   sqlite3.exe %db% ROLLBACK;
>   call stop_perform_another_scheduled_writings.cmd
>   call notify_admin_payment_writing_failed.cmd
>   )
>
>
> L.
>
>
>

no, because this script:

sqlite3.exe %db% BEGIN;
sqlite3.exe %db% COMMIT;

will end with: "Error: cannot commit - no transaction is active"
Every time sqlite3.exe is started a new transaction will begin (and END wehn 
sqlite3.exe stops)


while:
echo BEGIN; >file
echo COMMIT; >>file
cat file | sqlite3.exe %db%

works, creating 1 transaction.

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

Thanks to all answers. Are principles of such script OK, or I miss something?


set "_error="
sqlite3.exe %db% BEGIN;
for /f "tokens=2,3 delims=," %%a in (data.csv) do (
sqlite3 %db% "INSERT INTO payments(id,amount) VALUES 
('%%a','%%b')";
if errorlevel 1 set "_error=y"
)
if not defined _error (
sqlite3.exe %db% COMMIT;
) else (
sqlite3.exe %db% ROLLBACK;
call stop_perform_another_scheduled_writings.cmd
call notify_admin_payment_writing_failed.cmd
)


L.

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 6 Sep 2015, at 7:46pm, Petr L?z?ovsk?  wrote:

> Thanks to all answers. Are principles of such script OK, or I miss something?

Sorry you cannot script sqlite.exe like that.  Each time you run it is a 
separate session.  your BEGIN has no useful effect.

Simon.

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 6 Sep 2015, at 2:26pm, Yuriy M. Kaminskiy  wrote:

> Not quite. Even if some statement failed, if you COMMIT in the end, it
> will succeed and database will be (partially) modified.
> 
> It's application developer responsibility to check for errors and issue
> ROLLBACK instead of COMMIT, if desirable (BTW, in case of some
> errors^[], sqlite3 may cancel [and automatically ROLLBACK] transaction,
> it is application developer responsibility to check if transaction is
> still active after errors [with sqlite3_get_autocommit()]).

This is a very important part of SQLite and I keep forgetting it.  It has 
always seemed weird to me.  Thank you to Yuriy for correcting me.

Simon.

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 06-09-15 15:26, Yuriy M. Kaminskiy wrote:
> Luuk  writes:
>
>> DELETE FROM test;
>> BEGIN;
>> INSERT INTO test VALUES(1,'test1');
>> INSERT INTO test VALUES(3,'test3',3);
>> INSERT INTO test VALUES(2,'test2');
>> COMMIT;
>> SELECT * FROM test;
> (and normally I'd expect to see DELETE *inside* transaction);

to quoot Simon:

"But SQL is kind and opens a one-command transaction if you forget."

;)

This was intentionally before the BEGIN, because i 'wanted' it to be in 
it's own transaction.

[sqlite] BEGINNER - Transactions in shell script

[Yuriy M. Kaminskiy]

Luuk  writes:

> On 05-09-15 22:27, Simon Slavin wrote:
>> On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:
>>
>>> Have some shell scripts working with sqlite. Receiving incoming
>>> payments from bank via HTTP API and pushing it into database. This
>>> script will start periodically, every single hour.
>>>
>>> Want to prevent situation only few payments are written and script
>>> failed for some reason. Have read about sqlite transactions and
>>> understood so I should start every writing sequence with BEGIN;
>>> statement, than made all inserts and than COMMIT; statement. Is
>>> this all how it is working? Should I do something more to prevent
>>> such unwanted situation?
>> You have it correct.  If you put multiple change commands in one
>> transaction, then either they are all executed or none of them are
>> executed.

Not quite. Even if some statement failed, if you COMMIT in the end, it
will succeed and database will be (partially) modified.

It's application developer responsibility to check for errors and issue
ROLLBACK instead of COMMIT, if desirable (BTW, in case of some
errors^[], sqlite3 may cancel [and automatically ROLLBACK] transaction,
it is application developer responsibility to check if transaction is
still active after errors [with sqlite3_get_autocommit()]).

^[] IIRC, exact conditions are not documented, this is
implementation-defined behavior.

>> Even if your program crashes in the middle of a command,
>> when SQLite opens the file again it works out what happened and
>> restores a 'clean' database.

(yes, if program crashes (or closes connection) before COMMIT was
executed, it means "implicit ROLLBACK").

>> Theoretically you would have to always open a transaction for SQL.
>> Issuing INSERT before BEGIN would result in an error message.  But
>> SQL is kind and opens a one-command transaction if you forget.
>>
>
> Suppose i have 'test.sql':
> .echo on

Add
  .bail on
here.

> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> INSERT INTO test VALUES(3,'test3',3);
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;

(and normally I'd expect to see DELETE *inside* transaction);

> And a database 'test.sqlite' with the following table:
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE test(i int, t text);
> COMMIT;
>
> If i do:
> type test.sql | sqlite3.exe test.sqlite
>
> Than there is an error executing this line:
> INSERT INTO test VALUES(3,'test3',3);
>
> But the transaction is not stopped, or rolled back.
> What am i missing?



> Output of 'type test.sql | sqlite3.exe test.sqlite':
> DELETE FROM test;
> BEGIN;
> INSERT INTO test VALUES(1,'test1');
> Error: near line 5: table test has 2 columns but 3 values were supplied
> INSERT INTO test VALUES(2,'test2');
> COMMIT;
> SELECT * FROM test;
> 1|test1
> 2|test2

[sqlite] BEGINNER - Transactions in shell script

[Luuk]

On 05-09-15 22:27, Simon Slavin wrote:
> On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:
>
>> Have some shell scripts working with sqlite. Receiving incoming payments 
>> from bank via HTTP API and pushing it into database. This script will start 
>> periodically, every single hour.
>>
>> Want to prevent situation only few payments are written and script failed 
>> for some reason. Have read about sqlite transactions and understood so I 
>> should start every writing sequence with BEGIN; statement, than made all 
>> inserts and than COMMIT; statement. Is this all how it is working? Should I 
>> do something more to prevent such unwanted situation?
> You have it correct.  If you put multiple change commands in one transaction, 
> then either they are all executed or none of them are executed.  Even if your 
> program crashes in the middle of a command, when SQLite opens the file again 
> it works out what happened and restores a 'clean' database.
>
> Theoretically you would have to always open a transaction for SQL.  Issuing 
> INSERT before BEGIN would result in an error message.  But SQL is kind and 
> opens a one-command transaction if you forget.
>

Suppose i have 'test.sql':
.echo on
DELETE FROM test;
BEGIN;
INSERT INTO test VALUES(1,'test1');
INSERT INTO test VALUES(3,'test3',3);
INSERT INTO test VALUES(2,'test2');
COMMIT;
SELECT * FROM test;

And a database 'test.sqlite' with the following table:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int, t text);
COMMIT;

If i do:
type test.sql | sqlite3.exe test.sqlite

Than there is an error executing this line:
INSERT INTO test VALUES(3,'test3',3);

But the transaction is not stopped, or rolled back.
What am i missing?


Output of 'type test.sql | sqlite3.exe test.sqlite':
DELETE FROM test;
BEGIN;
INSERT INTO test VALUES(1,'test1');
Error: near line 5: table test has 2 columns but 3 values were supplied
INSERT INTO test VALUES(2,'test2');
COMMIT;
SELECT * FROM test;
1|test1
2|test2

[sqlite] BEGINNER - Transactions in shell script

[R.Smith]

On 2015-09-05 10:18 PM, Petr L?z?ovsk? wrote:
> Have some shell scripts working with sqlite. Receiving incoming payments from 
> bank via HTTP API and pushing it into database. This script will start 
> periodically, every single hour.
>
> Want to prevent situation only few payments are written and script failed for 
> some reason. Have read about sqlite transactions and understood so I should 
> start every writing sequence with BEGIN; statement, than made all inserts and 
> than COMMIT; statement. Is this all how it is working? Should I do something 
> more to prevent such unwanted situation?

It is indeed all you need. If the transaction fails an error will write 
to stdout (assuming you use the SQlite3.exe or other CLI) - it might be 
prudent in your scripts to watch for errors and call ROLLBACK (as 
opposed to COMMIT). Note that not all errors need to roll-back, You can 
specify in the schema the error handling, and some within the SQL 
statements. On the other hand you might wish to roll back in special 
cases even on non-error circumstances.

See:
http://www.sqlite.org/syntax/conflict-clause.html

in conjunction with:
http://www.sqlite.org/lang_createtable.html
and other constraint specifications.


Cheers,
Ryan

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

Have some shell scripts working with sqlite. Receiving incoming payments from 
bank via HTTP API and pushing it into database. This script will start 
periodically, every single hour. 

Want to prevent situation only few payments are written and script failed for 
some reason. Have read about sqlite transactions and understood so I should 
start every writing sequence with BEGIN; statement, than made all inserts and 
than COMMIT; statement. Is this all how it is working? Should I do something 
more to prevent such unwanted situation?

thanks, L. 

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:

> Have some shell scripts working with sqlite. Receiving incoming payments from 
> bank via HTTP API and pushing it into database. This script will start 
> periodically, every single hour. 
> 
> Want to prevent situation only few payments are written and script failed for 
> some reason. Have read about sqlite transactions and understood so I should 
> start every writing sequence with BEGIN; statement, than made all inserts and 
> than COMMIT; statement. Is this all how it is working? Should I do something 
> more to prevent such unwanted situation?

You have it correct.  If you put multiple change commands in one transaction, 
then either they are all executed or none of them are executed.  Even if your 
program crashes in the middle of a command, when SQLite opens the file again it 
works out what happened and restores a 'clean' database.

Theoretically you would have to always open a transaction for SQL.  Issuing 
INSERT before BEGIN would result in an error message.  But SQL is kind and 
opens a one-command transaction if you forget.

Simon.