Asked  7 Months ago    Answers:  5   Viewed   28 times

The MySQL documentation says that it should be '. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

 Answers

75

The MySQL documentation you cite actually says a little bit more than you mention. It also says,

A “'” inside a string quoted with “'” may be written as “''”.

(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)

I think the Postgres note on the backslash_quote (string) parameter is informative:

This controls whether a quote mark can be represented by ' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted '. However, use of ' creates security risks...

That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.

Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.

Tuesday, June 1, 2021
 
TheLovelySausage
answered 7 Months ago
29

Your use of the function is incorrect.

You MUST use the mysqli link resource returned by mysqli_connect as the first parameter to mysqli_real_escape_string.

Example:

$username="xxx";
$password="xxx";
$database="xxx";

$my = mysqli_connect('localhost',$username,$password);

mysqli_select_db($my, $database) or die( "Unable to select database");

$t->TeacherUsername = "'" . mysqli_real_escape_string($my, $teacherusername). "'";
Wednesday, March 31, 2021
 
Isky
answered 9 Months ago
45

Am I doing something horrendously wrong?

Yes.

First on your research.

Prepared Statements is the only great thing you have found.

While use of mysqli_real_escape_string (assuming you are using prepared statements) would be useless and harmful (producing the outcome you have noted yourself: “You’re name isn’t….”).

And Magic Quotes has been removed from the language long time ago already - thus, nothing to concern actually.

So, even most of your initial premises are plainly wrong.

Now to your question.

Couldn’t the query interpret the dollar sign as a PHP variable perhaps?

No.

What about LIKE syntax I’ve heard that uses the % symbol or even the wildcard sign?

Yes, you've heard it right. That's exact purpose of LIKE operator - to perform a wildcard search. Disabling these symbols in LIKE would make not a slightest sense.

Means every time you are going to use LIKE operator, you have to decide which particular symbol to use and which to disallow. NO one-for-all solution can be used. Not to mention that in all other mysql interactions % sign has no special meaning at all.

Prepared statements should technically take care of all of this

Prepared statements has nothing to do neither with $ nor with % signs. Prepared statements deal with SQL injections, but neither symbol could cause it (wouldn't you call "injection" a proper intended use of LIKE operator, would you?).

Finally, to the most horrendous part.

In the case you forget to use prepared statements or just neglect to do them,

nothing can save you.

And least help would be from the function you developed.

To sum it all up.

  1. Get rid of this function.
  2. Use placeholders* to represent every single variable in the query.
  3. Escape % and _ symbols in the input data only if it's going to be used in LIKE operator and you don't want them to be interpreted.
  4. Use htmlspecialchars() for output, not mysql input.

*read on prepared statements if the term is unfamiliar to you.

Friday, May 28, 2021
 
tdous
answered 7 Months ago
35

If you need to perform database operations, such as creating tables, then you should use SQL Server Management Objects instead of executing SQL strings.

For CRUD operations parameters is absolutely the only true path.

UPDATE: It appears that the MySQL client library contains a helper method for this ill-advised task. You can call MySqlHelper.EscapeString(string).

Saturday, July 31, 2021
 
waylaidwanderer
answered 5 Months ago
49

the cleanest way is to define a constant:

<property name="apos" scope="default" type="STRING" value="'"/>

and then use it as follow:

<property expression="fn:concat(get-property('whereConcat'),' AND PA_INATO=',get-property('apos'), get-property('cf'),get-property('apos'))" name="whereConcat" scope="default" type="STRING"/>
Saturday, August 28, 2021
 
letrollpoilu
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share