What is Arbitrary SQL?

[dzpt]

I'm a mysql user, and now i'm building a query with your class.
But it still getting error. I really don't understand what Arbitrary SQL is?
In mySQL or msSQL i can simply run a query like:
SELECT * FROM abc WHERE field='text'

I've tried it with your class but now an error occurred everytime.

let stmt = db!.prepare("SELECT COUNT(id) FROM dict WHERE word='?'")
            try stmt.run(inp)
            let count = stmt.scalar() as! Int64

I can't pass parameter (inp) in here.
What are .scalar , .prepare .run ??!

How to put parameter on it? And what if the input value has ' character?
Will it have sql injection?
Why you have to make it difficult to build an query?

[stephencelis]

I'm sorry that you're finding SQLite.swift difficult to use! I've tried to make the interface simple, but there are always blind spots. For the example you've provided, you haven't provided enough information to troubleshoot the issue, i.e.:

• What is inp?
• What is the error message?

SQLite.swift is a wrapper over the C interface. You seem to be having trouble binding values, which is described in depth here: https://www.sqlite.org/c3ref/bind_blob.html

Using ? (etc.) binds/escapes values for you so ' and injection are accounted for. So in your example, '?' should probably just be ?.

MySQL and SQLite are similar and both share a large overlap of standard SQL. What you describe as simple in MySQL sounds like you're running queries directly from the terminal. You can actually do this with SQLite, too!

For any differences, the SQLite3 web site is an invaluable (if deep) resource: http://sqlite.org

What you are describing as more difficult appears to be the C/Swift interface to SQLite (a problem that exists with MySQL libraries in most languages, too). They are documented, and you should be able to use Xcode and option/command-click on them for more information, but basically:

• run() simply runs a query and doesn't return any values.
• prepare() prepares a query (statement) to be used. Once prepared, you can call run or scalar() on it, iterate over the results, or unpack into an array.
• scalar() is simply a helper that fetches the first value of the first row (useful for scalar queries, which are quire common).

If you're still having trouble with SQLite or SQLite.swift, I'd recommend seeking help on Stack Overflow! I try to look for new questions there and answer when I can.

[dzpt]

inp is a String. i want to run build query in the simpliest way and less code as possible.

I've read your document and it seems there are two ways to build an query?
1 is build it by object oriented way (i don't what should i call)
2 is Executing Arbitrary SQL ? this way i simply can write down a query like mysql, mssql query...
Am i right?

I've removed the ' symbol and it works now. The trouble is if the keyword contains quote character, is it still running? should i be afraid of something like sql injection?

Many tks for your support

[stephencelis]

The ? is intended for binding/quoting/escaping raw values automatically (to prevent injection and accidental escaping). Closing this out now! Glad you got things sorted!

[PedroAnibarro1]

Hi!

Does anyone knows why is this giving me an error?

let tableInfo = try db.prepare("PRAGMA table_info(?)", tableName)

It saying in the error near "?": syntax error (code: 1)