I am using javascript/jquery to generate a sql query. I have a sql query I'm generating and using inside a javascript/jquery script.

Something like this:

var storeName;
var query = "SELECT * FROM stores where storeName = '" + storeName + "';";

(storeName is generated through jquery when a user selects from html)

So when storeName is something like "Jackson Deli" the query runs just fine.

But then when storeName is "Jackson's Deli" it does not work and it seems to be because the apostrophe in Jackson's is treated like a closing quote. I know I can escape a quote by doubling it if I was hard-coding the query... so

SELECT * FROM stores where storeName = 'Jackson''s Deli'; 

should work. But I'm not hard-coding the query. Instead it's being generated by user input and may or may not have an apostrophe in the name. How would I go about escaping ' this character in this case? I would need it to work inside Javascript/jquery.

Would I need to write an if statement that looks for ' in storeName and replaces it with '' ?? Or is there another way to go about this?

EDIT: Ouch! Normally, yes, I realize the perils of generating a query on the client side.
So here's some more context. I'm working with cartodb and following their documentation. Here's an example from their repo doing something similar to what I'm talking about (they have other examples too):

.js/blob/develop/examples/layer_selector.html

You can't run a query in cartodb that lets you modify data in any way -- you can only run queries that let you retrieve data. So I'm still thinking about what the best way to escape this quote character would be.

I am using javascript/jquery to generate a sql query. I have a sql query I'm generating and using inside a javascript/jquery script.

Something like this:

var storeName;
var query = "SELECT * FROM stores where storeName = '" + storeName + "';";

(storeName is generated through jquery when a user selects from html)

So when storeName is something like "Jackson Deli" the query runs just fine.

But then when storeName is "Jackson's Deli" it does not work and it seems to be because the apostrophe in Jackson's is treated like a closing quote. I know I can escape a quote by doubling it if I was hard-coding the query... so

SELECT * FROM stores where storeName = 'Jackson''s Deli'; 

should work. But I'm not hard-coding the query. Instead it's being generated by user input and may or may not have an apostrophe in the name. How would I go about escaping ' this character in this case? I would need it to work inside Javascript/jquery.

Would I need to write an if statement that looks for ' in storeName and replaces it with '' ?? Or is there another way to go about this?

EDIT: Ouch! Normally, yes, I realize the perils of generating a query on the client side.
So here's some more context. I'm working with cartodb and following their documentation. Here's an example from their repo doing something similar to what I'm talking about (they have other examples too):

https://github./CartoDB/cartodb.js/blob/develop/examples/layer_selector.html

You can't run a query in cartodb that lets you modify data in any way -- you can only run queries that let you retrieve data. So I'm still thinking about what the best way to escape this quote character would be.

• javascript
• jquery
• sql

• 10 Holy SQL injection batman. Who or where did you ever learn to create dynamic SQL strings on the client?>! – Brad M Commented Sep 6, 2013 at 14:33
• 3 Just curious, are you generating SQL queries on the client side? If anything, THAT takes SQL injection to a new level! The way to go about that would be to throw it as far as possible. Or even farther! – ppeterka Commented Sep 6, 2013 at 14:33
• 2 I really hope you're doing this in Node or Rhino. If this is being executed from a browser, your DB is toast. – giaour Commented Sep 6, 2013 at 14:34
• LOL @gvee that was the first thing I thought of. Afterwards I noticed that 2 people had actually upvoted this insane question! – Reinstate Monica Cellio Commented Sep 6, 2013 at 14:42
• @gvee I know that number 327 by heart now just from it being used as an answer on SO. – Ethan Commented Sep 6, 2013 at 14:42

5 Answers 5

DO NOT GENERATE SQL ON THE CLIENT SIDE... EVER

That being said, if you are going to use a dynamic query, you are best off escaping the user input and binding it to a prepared statement on the server side.

If you post more details about which database (MySQL, Postgres, etc.) and what language you are using for server processing- you will get better answers.

Yes... I am fully aware this doesn't answer the question. Nobody should be creating code this way though.

Edit: Made the warning bigger for emphasis.

I see others have answered but I wanted to approach this question from a few angles.

The question you're asking is a good one. You recognize that the SQL doesn't work with single quotes. You realize that something needs to be escaped. These are a good starting point for a few considerations that will hopefully help you to architect software in a secure and maintainable way.

1. Never directly execute client code/content - Generating SQL or any kind of code/instructions (javascript, bytecode, piled code) from a client is always a poor idea because it breaks a few critical concepts.

   • It's hard to maintain because you cannot control the input fully. Sure you could escape the SQL but that doesn't fix both strange case scenarios where you have other characters you didn't account for.
   • It isn't secure - Your relationship to variables, inputs, CGI params, file contents, database fields whose values came from the aforementioned list, or just about anything that came from a remote system, remote user cannot ever be trusted. Always check, sanitize and validate inputs. I can open the source to your page, see where you add a check for single quotes and change that and then execute the code to delete your records, have it email if certain stored procedures are available, run code on the SQL backend, drop databases (assuming the query runs under appropriate privileges.)
   • It blends/blurs the lines between client input/display and business logic. Research MVC, n-Tier development and other concepts for an introduction to the concepts of separating your business logic from display/inputs. This is critical not only for scalability and performance but also to reduce the change of issues such as this from causing critical security flaws.

2. Approach your software development from the bad-guys perspective - Instead of "How can I escape this string to make it work." try "How can I bypass the escape on this page to allow me to delete records, view things I should, etc.

3. Don't feel bad because the approach is wrong,learn from it. I see alot of ments about how you should never ever do this (and they're right) but many of us learned this lesson the hard way. We laugh at Little Bobby Tables because we've all written or had to support code that did this. The key is to understand the underpinning of why it's a bad idea and then use that in designing software. Wele to the school of hard knocks. We're all graduates and thankfully you could learn from our ments rather than when somebody tinkers and corrupts, deletes or infiltrates your database and application.

To get you started on this journey may I suggest reading the following: SQL Injections Explained

And as an added bonus XSS E.g. escaping OUTPUT that originated from an external system or person. for example a ment entry that contains Hi!!! <script>alert('Thanks to this site not escaping this output I get to run this code under your login. Thanks for the 4000 crates of free tshirts you just ordered for me');</script> how are you??? so that when you output it you get

Comments:Hi!!! <script>alert('Thanks to this site not escaping this output I get to run this code under your login.  Thanks for the 4000 crates of free tshirts you just ordered for me');</script> how are you???

Which is "valid" HTML and the browser will execute it.

Final thoughts - Adopt the motto Trust but Verify and you'll be OK

FYI, CartoDB does not allow you to execute a query that changes something in the table, it's read-only.

Send data to your server first, then escape all chars that need to be escaped with addslashes() mand (provided that you are using PHP).

addslashes() mand on PHP

After you are done with eascaping characters, you can send your data to cartoDB using their API and your API key.

cartoDB does provide insert/update/delete tasks through its SQL API. See this link:

http://developers.cartodb./documentation/sql-api.html