Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

BASH/PSQL whitespace issues

Go to solution Solved by Eigenvektor,
5 minutes ago, CJPowell27 said:

Interesting, so it basically doesn't concatenate properly by doing something like that? If what you are saying is the case, how do you recommend I get it to function as I intended?

From bash's point of view you are passing

--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="

as the argument for psql. As soon as bash encounters the second quote this argument is at an end. So anything that comes after that is treated as additional arguments.

 

So you need to write it as

--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName='$var2' and c.modelname='$var1';"

For bash to treat that as a single argument.

Hey guys I'm trying some PSQL integration with bash but I ran into an issue with variables containing spaces between words. I am basically getting two inputs of model names for both CPUs and Motherboards, then checking to see if those model names are the same socket in the database.

read -p "Enter CPU model name: " var1;
read -p "Enter motherboard name: " var2;

psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

But if I enter something with spaces such as: Ryzen 5 3600 the spaces will overflow into other variables in the psql query line. Below is a screenshot of the error.

image.png.2c3d2d1100374255044f0511ac37791c.png

 

If I enter the same thing (Ryzen 5 3600) but with dashes instead of spaces, this does not happen. Is there something I am missing with BASH user input that I would need to clear an input buffer or something? Thanks in advance for any tips

i5 4670k| Asrock H81M-ITX| EVGA Nex 650g| WD Black 500Gb| H100 with SP120s| ASUS Matrix 7970 Platinum (just sold)| Patriot Venom 1600Mhz 8Gb| Bitfenix Prodigy. Build log in progress 

Build Log here: http://linustechtips.com/main/topic/119926-yin-yang-prodigy-update-2-26-14/

Link to post
Share on other sites

Not sure but to me you are escaping the wrong type of quotes here

...where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

It should be the double quotes you need to escape, you wont need the single quotes

...where m.modelName=\"$var2\" and c.modelname=\"$var1\";";

 

Link to post
Share on other sites
6 minutes ago, C2dan88 said:

It should be the double quotes you need to escape, you wont need the single quotes

You need the single quotes inside the SQL

 

I just tried this on my PC:

read -p "Enter CPU model name: " var1
read -p "Enter motherboard name: " var2

echo "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";"

 

The output is as expected

Quote

./test.sh
Enter CPU model name: Ryzen 3600 CPU
Enter motherboard name: Some board


select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName='Some board' and c.modelname='Ryzen 3600 CPU';

 

~edit #2

Of course since you're inside a string already you can simply do

echo "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName='$var2' and c.modelname='$var1';"

There should be no need for escape sequences at all

Remember to quote or @mention others, so they are notified of your reply

Link to post
Share on other sites
4 minutes ago, Eigenvektor said:

You need the single quotes inside the SQL

Oh postrgres requires string values to be in only single quotes, whereas mysql you can use either type of quote.

Link to post
Share on other sites
Just now, C2dan88 said:

Oh postrgres requires string values to be in only single quotes, whereas mysql you can use either type of quote.

Correct. At this point I'm not sure if it is an issue with the read statement or the long select statement with a million different quotes. I tried doing a bit of "troubleshooting" by switching 

 

psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

 

into 

echo "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

and it printed the command perfectly with the single quotes in the correct spot with the proper values being filled in for the variables. That's what is throwing me completely off here 

i5 4670k| Asrock H81M-ITX| EVGA Nex 650g| WD Black 500Gb| H100 with SP120s| ASUS Matrix 7970 Platinum (just sold)| Patriot Venom 1600Mhz 8Gb| Bitfenix Prodigy. Build log in progress 

Build Log here: http://linustechtips.com/main/topic/119926-yin-yang-prodigy-update-2-26-14/

Link to post
Share on other sites
4 minutes ago, C2dan88 said:

Oh postrgres requires string values to be in only single quotes, whereas mysql you can use either type of quote.

However the quotes are the actual issue here 😄 Thanks, you just helped me figure out why.

 

psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

Thanks to using quotes OP is actually passing multiple strings to PSQL. This is effectively the same as passing:

 

psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="
\'$var2\'
" and c.modelname="
\'$var1\'
";";

Since the variables are not inside the string, a space translates into even more parameters for PSQL

 

1 minute ago, CJPowell27 said:

and it printed the command perfectly with the single quotes in the correct spot with the proper values being filled in for the variables. That's what is throwing me completely off here 

If you want to pass an argument with spaces to a program you need to put it into quotes, so

psql "a b c d"

 

Since you wrote "where field="'$var'" and …" the variable is actually outside the quotes, so if $var contains Ryzen 3600 CPU it translates into

 

psql "where field=" Ryzen 3600 CPU " and …", so multiple parameters.

Remember to quote or @mention others, so they are notified of your reply

Link to post
Share on other sites
3 minutes ago, Eigenvektor said:

However the quotes are the actual issue here 😄 Thanks, you just helped me figure out why.

 


psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="\'$var2\'" and c.modelname="\'$var1\'";";

Thanks to using quotes OP is actually passing multiple strings to PSQL. This is effectively the same as passing:

 


psql -d "$dbname" --command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="
\'$var2\'
" and c.modelname="
\'$var1\'
";";

Since the variables are not inside the string, a space translates into even more parameters for PSQL

 

If you want to pass an argument with spaces to a program you need to put it into quotes, so

psql "a b c d"

 

Since you wrote "where field="'$var'" and …" the variable is actually outside the quotes, so if $var contains Ryzen 3600 CPU it translates into

 

psql "where field=" Ryzen 3600 CPU " and …", so multiple parameters.

Interesting, so it basically doesn't concatenate properly by doing something like that? If what you are saying is the case, how do you recommend I get it to function as I intended?

i5 4670k| Asrock H81M-ITX| EVGA Nex 650g| WD Black 500Gb| H100 with SP120s| ASUS Matrix 7970 Platinum (just sold)| Patriot Venom 1600Mhz 8Gb| Bitfenix Prodigy. Build log in progress 

Build Log here: http://linustechtips.com/main/topic/119926-yin-yang-prodigy-update-2-26-14/

Link to post
Share on other sites
5 minutes ago, CJPowell27 said:

Interesting, so it basically doesn't concatenate properly by doing something like that? If what you are saying is the case, how do you recommend I get it to function as I intended?

From bash's point of view you are passing

--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="

as the argument for psql. As soon as bash encounters the second quote this argument is at an end. So anything that comes after that is treated as additional arguments.

 

So you need to write it as

--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName='$var2' and c.modelname='$var1';"

For bash to treat that as a single argument.

Remember to quote or @mention others, so they are notified of your reply

Link to post
Share on other sites
14 minutes ago, C2dan88 said:

Oh postrgres requires string values to be in only single quotes, whereas mysql you can use either type of quote.

A single quote is the SQL standard and works in MariaDB, MySQL, PSQL, MSSQL, Oracle etc. MySQL is… rather lax when it comes to adhering to SQL standards, so I'm not surprised it accepts them. I would definitely stick to single quotes in SQL.

Remember to quote or @mention others, so they are notified of your reply

Link to post
Share on other sites
4 minutes ago, Eigenvektor said:

From bash's point of view you are passing


--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName="

as the argument for psql. As soon as bash encounters the second quote this argument is at an end. So anything that comes after that is treated as additional arguments.

 

So you need to write it as


--command "select * from cpu c inner join motherboard m on c.socket=m.socket where m.modelName='$var2' and c.modelname='$var1';"

For bash to treat that as a single argument.

Good lord I was making that far too difficult for myself, I must be burnt out. Thank you so much, it worked!

i5 4670k| Asrock H81M-ITX| EVGA Nex 650g| WD Black 500Gb| H100 with SP120s| ASUS Matrix 7970 Platinum (just sold)| Patriot Venom 1600Mhz 8Gb| Bitfenix Prodigy. Build log in progress 

Build Log here: http://linustechtips.com/main/topic/119926-yin-yang-prodigy-update-2-26-14/

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×