New Posts  All Forums:Forum Nav:

Shell Scripting.

post #1 of 3
Thread Starter 
I want to execute SQL from a server so I am using ISQL. However, the SQL is dynamic so I have to build it on the fly but ISQL only accepts files. What I'm doing now is echoing the command into a temp file and then loading the temp file. Is there a way to skip the temp file? Basically, pass a virtual file to ISQL?

As an example:
echo -e "select * from table\ngo" > /temp/sqltest.sql | isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql


Any way I can get rid of /temp/sqltest.sql so it becomes more like:
isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < echo -e "select * from table\ngo"
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #2 of 3
Quote:
Originally Posted by DuckieHo View Post

I want to execute SQL from a server so I am using ISQL. However, the SQL is dynamic so I have to build it on the fly but ISQL only accepts files. What I'm doing now is echoing the command into a temp file and then loading the temp file. Is there a way to skip the temp file? Basically, pass a virtual file to ISQL?

As an example:
echo -e "select * from table\ngo" > /temp/sqltest.sql | isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql


Any way I can get rid of /temp/sqltest.sql so it becomes more like:
isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < echo -e "select * from table\ngo"

Is this primarily to to prevent IO? If so, just use a /tmp location. Most new Linux distros store all files in this location in RAM (unless there is a reason to write to disk). You could also mount a new ramfs at the location of your choice. Make sure you use mktemp (interesting article on why. The short version is to prevent predictable filenames (reducing attack vectors) and also to prevent multiple script instances from generating a race condition).

Finally, I don't know for sure, but you may be able to pass the string using xargs or by assigning a variable name.
Edited by hajile - 4/5/13 at 7:00am
post #3 of 3
Quote:
Originally Posted by DuckieHo View Post

I want to execute SQL from a server so I am using ISQL. However, the SQL is dynamic so I have to build it on the fly but ISQL only accepts files. What I'm doing now is echoing the command into a temp file and then loading the temp file. Is there a way to skip the temp file? Basically, pass a virtual file to ISQL?

As an example:
echo -e "select * from table\ngo" > /temp/sqltest.sql | isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql


Any way I can get rid of /temp/sqltest.sql so it becomes more like:
isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < echo -e "select * from table\ngo"
Sorry to be a pain but you're whole design concept there is wrong. You're opening yourself up to SQL injection attacks and all sorts by doing it this way. And even if this is a personal project and not running on any internet-facing services, it's still very messy and hard to control (ie there's several places this could silently break and ISQL would still try carry on - which is very dangerous).

If you need this to be a CLI tool, then you should knock up a quick Perl interface.

However you're not stupid, so I'm guessing you're going to come up with some exotic scenario why you're stuck with this kludge of a routine. so in there interest of making this a little more robust (read: it's still a terrible solution, but you're less likely to blow your whole leg off with these edits), heres a rewrite:
Code:
echo -e "select * from table\ngo" > /temp/sqltest.sql 2>&1 && isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql
this will only run isql if the output gets written to disk successfully, and if echo does fail then the function will return the error message instead.

Next remove the double quotes for single quotes so that and strings with a dollar sign doesn't get translated into environmental variables:
Code:
echo -e 'select * from table\ngo' > /temp/sqltest.sql 2>&1 && isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql

and lastly, we need to strip quotations from you input otherwise you'll basically ruin your server:
Code:
#!/bin/sh
$sql=`sed -e "s/'//g" < /dev/stdin`
echo -e $sql > /temp/sqltest.sql 2>&1 && isql -Uuser -Sserver -Ddb -Ppassword -e -o /temp/sqltest.log -w5000 < /temp/sqltest.sql
I've also set that to read from the STDIN so you can save that as sql.sh and then pipe commands into it like any other shell script.

But really, the amount of cleansing you can do from a shell script like this is pretty minimal (even with sed). You're far far better off re-writing the whole thing in Perl or Python.

Again, I'm sorry that I'm ripping your code to shreds, but what you've presented is a really dangerous routine to solve what's actually a very common problem smile.gif
Edited by Plan9 - 4/8/13 at 5:50am
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming