BASH/PSQL whitespace issues

[CJPowell27]

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.

 

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

[C2dan88]

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\";";

 

[Eigenvektor]

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

[C2dan88]

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.

[CJPowell27]

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 

[Eigenvektor]

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.

[CJPowell27]

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?

[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.

[Eigenvektor]

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.

[CJPowell27]

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!