0

I am a beginner in SQL, and I was having some trouble with special characters like parentheses and asterisks in user generated data. So far, I have mostly been using a lot of ad hoc methods of getting rid of these characters and they work well enough. Based on what I have read, I think paramaterized queries might be a more systematic way of getting around some of the problems that I have.

I have following query:

insert into midstep (street)(select street from addresses limit 10)

The column street in the table addresses has a lot of parantheses, asterisks etc. The code above works as expected. What I want to do is something like this:

prepare midstreet (text) AS insert into midstep (street)(select $1 from addresses limit 10);
 execute midstreet ( street from addresses);

However, when I enter in that code I get the following error message:

ERROR: syntax error at or near "from"
SQL state: 42601
Character: 29

I have tried a bunch of variations on this code and read through the documentation on Prepare and Execute but always get error messages. Any help is appreciated!

EDIT: I forgot to mention, I am using postgresql 9.3 on and my os is Ubuntu. Please ask if you need any more information to help!

Ravi Mehta
  • 485
  • 1
  • 6
  • 15

1 Answers1

0

You do not need a parameter in your case, as you get your values using a sub-query. As a matter of fact, you cannot know the needed values on server-side. Parameters like that are needed if and only if you have some values defined on application level and you want to pass them to the database.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • If parameterizing isn't the way to go, what would you reccommend to get rid of parantheses and asterisks? I will be passing this data into a geocoder that doesn't know how to deal with parentheses and asterisks. – Ravi Mehta Nov 02 '14 at 00:19
  • When you are querying inside the RDBMS, there is no danger of SQL injection. SQL injection is a possible danger when you are querying something and adding parameters from outside the RDBMS. If you are having a value, like I'm hungry, you will not get errors because of the '. The problem is when you generate your query and the result looks like this: select * from users where username = ''; delete from users;' – Lajos Arpad Nov 02 '14 at 01:56