How to use '?' in SQL query

Question

Suppose I have this query:

SELECT * FROM 'users' WHERE ( username = 'foo' OR email = 'bar' )

How would I make it so, that query uses ? marks instead of passing values in directly. eg.

SELECT * FROM 'users' WHERE ( username = ? OR email = ? ...

And have input specified within WHERE () clauses if possible.

EDIT: I've read somewhere a while ago that using ? marks in queries improves performance and helps with sql injections. This is what I am trying to do here.

What flavour of SQL? Typically this would be a function of the client/code that is sending the query to the database server — AndrewP
– AndrewP, Commented Feb 18, 2013 at 0:44
? is used to define place holders. And are used with prepared statements. What is it that you are trying to do? — Starx
– Starx, Commented Feb 18, 2013 at 0:48
I am trying to do SELECT * FROM 'users' WHERE ( 'username = ? OR email = ?', 'foo', 'bar') so foo and bar replace placeholders. — if __name__ is None
– if __name__ is None, Commented Feb 18, 2013 at 0:56
Are you executing your SQL from the mysql/psql interface? From within a procedure? Or perhaps through some language's database interface? And are you using MySQL or PostgreSQL? I'm guessing the former since PostgreSQL would complain about 'users'. — mu is too short
– mu is too short, Commented Feb 18, 2013 at 1:31

John Woo · Accepted Answer · 2013-02-18 01:00:24Z

8

In MySQL, the question mark symbol that you can find in your query is called the parameter placeholder. It is usually used when you are creating a Prepare statement (dynamic sql). ex,

SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
PREPARE stmt2 FROM @s;
SET @a = 6;
SET @b = 8;
EXECUTE stmt2 USING @a, @b;

UPDATE 1

here's an example of select based on your data above,

SET @s = 'SELECT * FROM tableName WHERE username = ? and email = ?';
PREPARE stmt2 FROM @s;
SET @a = 'foo';
SET @b = 'bar';
EXECUTE stmt2 USING @a, @b;

SQLFiddle Demo

edited Feb 18, 2013 at 1:00

answered Feb 18, 2013 at 0:46

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

if __name__ is None Over a year ago

Is it possible to fill placeholders and execute SELECT query in single command?

John Woo Over a year ago

what do you mean by fill placeholders? you can create a stored procedure from that.

if __name__ is None Over a year ago

Im wondering if its possible to use placeholders, provide values for placeholders and execute query in same sql command.

John Woo Over a year ago

if that's the case, why do you still want to use place holders when you have already a value in line on the query?

if __name__ is None Over a year ago

oh yea, now I understand what ? marks do. And I figured out how to use them in the ORM Im using. Like so: User.where("username = ? OR email = ?", username, email)[0]

Collectives™ on Stack Overflow

How to use '?' in SQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related