Source SQL Escaping

[Bake Mono]

Sql_Query(mmysql_handle, "INSERT INTO `tableName` (`id`, `char_id`, `char_name`, `column1`, `column2`,) VALUES ('%d', '%d', '%s', '%s', '%s')", sd->vender_id, sd->bl.id, sd->status.name, message, mapindex_id2name(sd->mapindex));

That's essentially the line of code I have in one of my source files. It works great, no errors or anything. However when a player opens a chatroom for example with a name containing quotes ( ' , " ), the map server naturally throws an error because of malformed SQL. What I'm wondering is, how would I add to this to escape the string to prevent any sort of abuse that can come of it? I want to keep the quotes in there, yet protect it from any sort of injection. I'm not too familiar with C#, but I haven't been able to find a somewhat simple way of doing this.

Edited January 25, 2012 by Bake Mono

[KeyWorld]

You can use Sql_EscapeStringLen.

[Bake Mono]

You can use Sql_EscapeStringLen.

How would the syntax follow for that? From what I gathered by looking at other source code, I got the arguments as Sql_EscapeStringLen(handle, variable, variable string being escaped, length)

With that, my code now reads:

Sql_EscapeStringLen(mmysql_handle, newmes, message, strnlen(message, len));
Sql_Query(mmysql_handle, "INSERT INTO `tableName` (`id`, `char_id`, `char_name`, `column1`, `column2`,) VALUES ('%d', '%d', '%s', '%s', '%s')", sd->vender_id, sd->bl.id, sd->status.name, newmes, mapindex_id2name(sd->mapindex));

When compiling, it spurts out the error (which I can see why) as

error C2065: 'newmes' : undeclared identifier

warning C4047: 'function' : 'char *' differs in levels of indirection from 'int'

warning C4024: 'Sql_EscapeStringLen' : different types for formal and actual parameter 2

error C2065: 'len' : undeclared identifier

error C2065: 'newmes' : undeclared identifier

Now my question is, how would I declare such identifiers, or must I use something already declared?

Edited January 24, 2012 by Bake Mono

[Gepard]

char esc_mes[MESSAGE_SIZE*2+1]; // make sure size is 2*original size+1
Sql_EscapeString(sql_handle, esc_mes, message);

[Bake Mono]

char esc_mes[MESSAGE_SIZE*2+1]; // make sure size is 2*original size+1
Sql_EscapeString(sql_handle, esc_mes, message);

Thanks for helping guys. But, that comes back to the same problem of saying something is an undeclared identifier (i.e. esc_mes), as well as a couple new errors. Here's a quote of the main code, if it'll help make it easier to see what I'm seeing:

/*==========================================
* Open shop
* data := {<index>.w <amount>.w <value>.l}[count]
*------------------------------------------*/
void vending_openvending(struct map_session_data* sd, const char* message, bool flag, const uint8* data, int count)
{
int i, j;
int vending_skill_lvl;
nullpo_retv(sd);

if( !flag ) // cancelled
	return; // nothing to do

if ( pc_isdead(sd) || !sd->state.prevend || pc_istrading(sd))
	return; // can't open vendings lying dead || didn't use via the skill (wpe/hack) || can't have 2 shops at once

vending_skill_lvl = pc_checkskill(sd, MC_VENDING);
// skill level and cart check
if( !vending_skill_lvl || !pc_iscarton(sd) )
{
	clif_skill_fail(sd, MC_VENDING, USESKILL_FAIL_LEVEL, 0);
	return;
}

// check number of items in shop
if( count < 1 || count > MAX_VENDING || count > 2 + vending_skill_lvl )
{	// invalid item count
	clif_skill_fail(sd, MC_VENDING, USESKILL_FAIL_LEVEL, 0);
	return;
}

// filter out invalid items
i = 0;
for( j = 0; j < count; j++ )
{
	short index		= *(uint16*)(data + 8*j + 0);
	short amount	   = *(uint16*)(data + 8*j + 2);
	unsigned int value = *(uint32*)(data + 8*j + 4);

	index -= 2; // offset adjustment (client says that the first cart position is 2)

	if( index < 0 || index >= MAX_CART // invalid position
	||  pc_cartitem_amount(sd, index, amount) < 0 // invalid item or insufficient quantity
	//NOTE: official server does not do any of the following checks!
	||  !sd->status.cart[index].identify // unidentified item
	||  sd->status.cart[index].attribute == 1 // broken item
	||  sd->status.cart[index].expire_time // It should not be in the cart but just in case
	||  !itemdb_cantrade(&sd->status.cart[index], pc_isGM(sd), pc_isGM(sd)) ) // untradeable item
		continue;

	sd->vending[i].index = index;
	sd->vending[i].amount = amount;
	sd->vending[i].value = cap_value(value, 0, (unsigned int)battle_config.vending_max_value);

<hidden after problem solved>

	i++; // item successfully added
}

if( i != j )
	clif_displaymessage (sd->fd, msg_txt(266)); //"Some of your items cannot be vended and were removed from the shop."

if( i == 0 )
{	// no valid item found
	clif_skill_fail(sd, MC_VENDING, USESKILL_FAIL_LEVEL, 0); // custom reply packet
	return;
}
sd->state.prevend = 0;
sd->state.vending = true;
sd->vender_id = vending_getuid();
sd->vend_num = i;
safestrncpy(sd->message, message, MESSAGE_SIZE);

pc_stop_walking(sd,1);
clif_openvending(sd,sd->bl.id,sd->vending);
clif_showvendingboard(&sd->bl,message,0);
char esc_mes[MESSAGE_SIZE*2+1];
Sql_EscapeString(mmysql_handle, esc_mes, message);
<hidden after problem solved>

}


/// Checks if an item is being sold in given player's vending.
bool vending_search(struct map_session_data* sd, unsigned short nameid)
{
....

The error message being:

1>..srcmapvending.c(315): error C2143: syntax error : missing ';' before 'type'

1>..srcmapvending.c(316): error C2065: 'esc_mes' : undeclared identifier

1>..srcmapvending.c(316): warning C4047: 'function' : 'char *' differs in levels of indirection from 'int'

1>..srcmapvending.c(316): warning C4024: 'Sql_EscapeString' : different types for formal and actual parameter 2

1>..srcmapvending.c(323): error C2061: syntax error : identifier 'vending_search'

1>..srcmapvending.c(323): error C2059: syntax error : ';'

1>..srcmapvending.c(323): error C2059: syntax error : 'type'

</value></amount></index>

Edited January 25, 2012 by Bake Mono

[Aleos]

You need to put your declaration at the top, not midway through.

int i, j;
int vending_skill_lvl;
char esc_mes[MESSAGE_SIZE*2+1];
nullpo_retv(sd);

[Bake Mono]

That's just... awesome. I had a feeling it might do something like that (unlike php or normal scripting where you can declare them wherever you want). When I tried, I guess I put it at the top, just not in that exact spot. Thanks for the help everyone.